schema.sql

-- Run this in your database (e.g. USE test; or USE resqnow;) if you prefer manual setup.
-- The server also creates this table automatically on startup when using TiDB/MySQL.
CREATE TABLE IF NOT EXISTS technicians (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255),
  phone VARCHAR(50),
  proprietor_name VARCHAR(255),
  alternate_phone VARCHAR(50),
  whatsapp_number VARCHAR(50),
  service_type VARCHAR(100),
  location VARCHAR(255),
  address VARCHAR(512),
  region VARCHAR(255),
  district VARCHAR(255),
  state VARCHAR(255),
  locality VARCHAR(255),
  google_maps_link VARCHAR(512),
  aadhaar_number VARCHAR(50),
  pan_number VARCHAR(50),
  business_type VARCHAR(50),
  gst_number VARCHAR(50),
  trade_license_number VARCHAR(50),
  service_area_range INT DEFAULT 10,
  experience INT DEFAULT 0,
  specialties JSON,
  pricing JSON,
  working_hours JSON,
  service_costs JSON,
  payment_details JSON,
  app_readiness JSON,
  vehicle_types JSON,
  documents JSON,
  resume_url VARCHAR(512),
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  is_active BOOLEAN DEFAULT TRUE,
  is_available BOOLEAN DEFAULT FALSE,
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS technician_approval_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  technician_id INT NOT NULL,
  action ENUM('approved', 'rejected') NOT NULL,
  previous_status VARCHAR(50),
  new_status VARCHAR(50) NOT NULL,
  reason TEXT,
  admin_email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (technician_id) REFERENCES technicians(id)
);