USE `skillst1_rngkqnhl_printportal`;

-- V9 STAFF SERVICE APPLY / LEAD SYSTEM
-- Run these statements only once.

CREATE TABLE IF NOT EXISTS `leads` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `customer_id` INT NULL,
  `name` VARCHAR(150) NOT NULL,
  `father_name` VARCHAR(150) NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `whatsapp_no` VARCHAR(20) NULL,
  `service_id` INT NULL,
  `source` VARCHAR(50) DEFAULT 'Staff',
  `status` ENUM('NEW','CONTACTED','CONVERTED','LOST') DEFAULT 'NEW',
  `notes` TEXT NULL,
  `created_by` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`mobile`),
  INDEX (`status`)
);

CREATE TABLE IF NOT EXISTS `staff_wallet_transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `staff_id` INT NOT NULL,
  `type` ENUM('credit','debit') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `reference_type` VARCHAR(40) NULL,
  `reference_id` INT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`staff_id`),
  INDEX (`reference_id`)
);

CREATE TABLE IF NOT EXISTS `staff_wallet` (
  `staff_id` INT PRIMARY KEY,
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS `required_documents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `service_id` INT NOT NULL,
  `document_name` VARCHAR(150) NOT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`service_id`)
);

-- Service Apply log: keeps the exact wallet deduction made by staff.
CREATE TABLE IF NOT EXISTS `service_apply_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `application_id` INT NOT NULL,
  `staff_id` INT NOT NULL,
  `wallet_charge` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `whatsapp_no` VARCHAR(20) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (`application_id`),
  INDEX (`staff_id`)
);