-- BALIMEEN PRIVATE LIMITED - Employee Document Verification & Branch Hierarchy
-- Choice: 1 (PHP + MySQL)

-- 1. Branch Management
CREATE TABLE IF NOT EXISTS `branches` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `branch_name` VARCHAR(100) NOT NULL,
    `branch_type` ENUM('Head Office', 'Factory', 'Warehouse', 'Regional Office', 'Sales Office') NOT NULL,
    `location` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Update Employees Table for Branch & Verification
ALTER TABLE `employees` 
ADD COLUMN `branch_id` INT NULL AFTER `department`,
ADD COLUMN `verification_status` ENUM('Pending', 'Under Review', 'Correction Needed', 'Verified', 'Rejected') DEFAULT 'Pending';

-- 3. Employee Documents
CREATE TABLE IF NOT EXISTS `employee_documents` (
    `doc_id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `category` ENUM('Identity', 'Address', 'Banking', 'Education', 'Employment', 'Compliance') NOT NULL,
    `document_type` VARCHAR(100) NOT NULL,
    `document_number` VARCHAR(100) NULL,
    `file_url` VARCHAR(255) NULL,
    `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `expiry_date` DATE NULL,
    `status` ENUM('Pending Upload', 'Submitted', 'Under Review', 'Correction Needed', 'Approved', 'Rejected', 'Expired') DEFAULT 'Pending Upload',
    `remarks` TEXT NULL,
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
);

-- 4. Verification Logs
CREATE TABLE IF NOT EXISTS `verification_logs` (
    `log_id` INT AUTO_INCREMENT PRIMARY KEY,
    `doc_id` INT NOT NULL,
    `verified_by` INT NOT NULL, -- Admin or HR ID
    `action` VARCHAR(50) NOT NULL,
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `comments` TEXT NULL,
    FOREIGN KEY (`doc_id`) REFERENCES `employee_documents`(`doc_id`) ON DELETE CASCADE
);

-- 5. Background Checks
CREATE TABLE IF NOT EXISTS `background_checks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `police_status` ENUM('Pending', 'In Progress', 'Clear', 'Flagged') DEFAULT 'Pending',
    `medical_status` ENUM('Pending', 'In Progress', 'Clear', 'Flagged') DEFAULT 'Pending',
    `reference_check` ENUM('Pending', 'In Progress', 'Clear', 'Flagged') DEFAULT 'Pending',
    `final_clearance` ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
);
