-- SSCI Office Platform Database Schema
-- Shepherd Security and Consultancy International

-- Settings Table
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `site_name` VARCHAR(255) DEFAULT 'SSCI Office',
    `site_acronym` VARCHAR(50) DEFAULT 'SSCI',
    `logo` VARCHAR(255),
    `letterhead` VARCHAR(255),
    `primary_color` VARCHAR(7) DEFAULT '#2563eb',
    `whatsapp_api_token` TEXT,
    `sms_gateway_token` TEXT,
    `campay_api_token` TEXT,
    `email_enabled` BOOLEAN DEFAULT FALSE,
    `sms_enabled` BOOLEAN DEFAULT FALSE,
    `push_enabled` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'manager', 'controller', 'guard', 'client') DEFAULT 'guard',
    `phone` VARCHAR(20),
    `whatsapp` VARCHAR(20),
    `address` TEXT,
    `photo` VARCHAR(255),
    `active` BOOLEAN DEFAULT FALSE,
    `verification_token` VARCHAR(255),
    `verified_at` TIMESTAMP NULL,
    `reset_token` VARCHAR(255),
    `reset_token_expiry` TIMESTAMP NULL,
    `last_login` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `email` (`email`),
    INDEX `role` (`role`),
    INDEX `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clients Table
CREATE TABLE IF NOT EXISTS `clients` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255),
    `phone` VARCHAR(20),
    `whatsapp` VARCHAR(20),
    `address` TEXT,
    `city` VARCHAR(100),
    `country` VARCHAR(100),
    `contact_person` VARCHAR(255),
    `contact_title` VARCHAR(100),
    `company_type` VARCHAR(100),
    `registration_number` VARCHAR(100),
    `tax_id` VARCHAR(100),
    `active` BOOLEAN DEFAULT TRUE,
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `name` (`name`),
    INDEX `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Posts Table (Security Posts/Facilities)
CREATE TABLE IF NOT EXISTS `posts` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `facility_type` VARCHAR(100),
    `owner_id` INT,
    `location` TEXT,
    `owner_phone` VARCHAR(20),
    `owner_address` TEXT,
    `latitude` DECIMAL(10, 8),
    `longitude` DECIMAL(11, 8),
    -- Assessment Fields
    `assessment_security_post` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_fence` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_cctv_alarms` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_fire_extinguishers` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_lighting` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_toilet` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_accessibility` ENUM('Good', 'Fair', 'Poor') DEFAULT 'Fair',
    `assessment_crime_rate` ENUM('High', 'Medium', 'Low') DEFAULT 'Medium',
    `assessment_public_security_proximity` ENUM('Close', 'Moderate', 'Far') DEFAULT 'Moderate',
    `assessment_living_standards` ENUM('High', 'Medium', 'Low') DEFAULT 'Medium',
    `assessment_labour_availability` ENUM('Abundant', 'Moderate', 'Scarce') DEFAULT 'Moderate',
    `content_value` ENUM('High', 'Medium', 'Low') DEFAULT 'Medium',
    -- Facility Details
    `observations` TEXT,
    `recommendations` TEXT,
    `surface_area` DECIMAL(10, 2),
    `access_points` INT,
    `num_buildings` INT,
    `grand_total` DECIMAL(12, 2) DEFAULT 0,
    `services` JSON,
    -- Contract Details
    `contract_date` DATE,
    `contract_end` DATE,
    `status` ENUM('Active', 'Inactive', 'Closed') DEFAULT 'Active',
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `owner_id` (`owner_id`),
    INDEX `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Guards Controls Table
CREATE TABLE IF NOT EXISTS `guards_controls` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `post_id` INT NOT NULL,
    `guard_id` INT NOT NULL,
    `controller_id` INT NOT NULL,
    `control_datetime` DATETIME NOT NULL,
    `latitude` DECIMAL(10, 8),
    `longitude` DECIMAL(11, 8),
    -- Evaluation Criteria (1-5 scale)
    `attendance_punctuality` INT CHECK(attendance_punctuality >= 1 AND attendance_punctuality <= 5),
    `uniform_presentation` INT CHECK(uniform_presentation >= 1 AND uniform_presentation <= 5),
    `patrol_reporting_discipline` INT CHECK(patrol_reporting_discipline >= 1 AND patrol_reporting_discipline <= 5),
    `client_feedback` INT CHECK(client_feedback >= 1 AND client_feedback <= 5),
    `controller_general_evaluation` INT CHECK(controller_general_evaluation >= 1 AND controller_general_evaluation <= 5),
    `notes` TEXT,
    `photo_path` VARCHAR(500) NULL,
    `total_score` INT,
    `status` ENUM('Pending', 'Completed', 'Reviewed') DEFAULT 'Pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`guard_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`controller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `post_id` (`post_id`),
    INDEX `guard_id` (`guard_id`),
    INDEX `controller_id` (`controller_id`),
    INDEX `control_datetime` (`control_datetime`),
    INDEX `photo_path` (`photo_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Controllers Evaluation Table
CREATE TABLE IF NOT EXISTS `controllers_evaluations` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `controller_id` INT NOT NULL,
    `evaluation_period` VARCHAR(7), -- Format: YYYY-MM
    -- Evaluation Criteria (1-5 scale)
    `control_coverage_frequency` INT CHECK(control_coverage_frequency >= 1 AND control_coverage_frequency <= 5),
    `reporting_quality` INT CHECK(reporting_quality >= 1 AND reporting_quality <= 5),
    `complaint_resolution` INT CHECK(complaint_resolution >= 1 AND complaint_resolution <= 5),
    `guard_management` INT CHECK(guard_management >= 1 AND guard_management <= 5),
    `marketing_lead_contribution` INT CHECK(marketing_lead_contribution >= 1 AND marketing_lead_contribution <= 5),
    `notes` TEXT,
    `total_score` INT,
    `reviewed_by` INT,
    `reviewed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`controller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `controller_id` (`controller_id`),
    INDEX `evaluation_period` (`evaluation_period`),
    UNIQUE KEY `unique_evaluation` (`controller_id`, `evaluation_period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Managers Evaluation Table
CREATE TABLE IF NOT EXISTS `managers_evaluations` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `manager_id` INT NOT NULL,
    `evaluation_period` VARCHAR(7), -- Format: YYYY-MM
    -- Evaluation Criteria (1-5 scale)
    `operational_efficiency` INT CHECK(operational_efficiency >= 1 AND operational_efficiency <= 5),
    `client_relationship_management` INT CHECK(client_relationship_management >= 1 AND client_relationship_management <= 5),
    `hr_coordination` INT CHECK(hr_coordination >= 1 AND hr_coordination <= 5),
    `administrative_compliance` INT CHECK(administrative_compliance >= 1 AND administrative_compliance <= 5),
    `marketing_expansion_engagement` INT CHECK(marketing_expansion_engagement >= 1 AND marketing_expansion_engagement <= 5),
    `notes` TEXT,
    `total_score` INT,
    `reviewed_by` INT,
    `reviewed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`manager_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `manager_id` (`manager_id`),
    INDEX `evaluation_period` (`evaluation_period`),
    UNIQUE KEY `unique_evaluation` (`manager_id`, `evaluation_period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoices Table
CREATE TABLE IF NOT EXISTS `invoices` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `invoice_number` VARCHAR(50) UNIQUE NOT NULL,
    `owner_id` INT NOT NULL,
    `post_id` INT,
    `invoice_date` DATE NOT NULL,
    `due_date` DATE,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `subtotal` DECIMAL(15, 2) DEFAULT 0,
    `tax_amount` DECIMAL(15, 2) DEFAULT 0,
    `total_amount` DECIMAL(15, 2) DEFAULT 0,
    `status` ENUM('Draft', 'Sent', 'Partial', 'Paid', 'Overdue', 'Cancelled') DEFAULT 'Draft',
    `notes` TEXT,
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `invoice_number` (`invoice_number`),
    INDEX `owner_id` (`owner_id`),
    INDEX `status` (`status`),
    INDEX `invoice_date` (`invoice_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoice Line Items
CREATE TABLE IF NOT EXISTS `invoice_items` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `invoice_id` INT NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `quantity` DECIMAL(10, 2) DEFAULT 1,
    `unit_price` DECIMAL(15, 2) NOT NULL,
    `line_total` DECIMAL(15, 2) NOT NULL,
    FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE,
    INDEX `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments Table (From Clients and To Workers)
CREATE TABLE IF NOT EXISTS `payments` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `reference_number` VARCHAR(50) UNIQUE NOT NULL,
    `payment_type` ENUM('Client_Income', 'Staff_Salary', 'Staff_Expense', 'Company_Expense') NOT NULL,
    `related_id` INT, -- Could be invoice_id, user_id, or expense_id
    `amount` DECIMAL(15, 2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `payment_method` ENUM('Cash', 'Bank Transfer', 'Check', 'Credit Card', 'Mobile Money') DEFAULT 'Bank Transfer',
    `payment_date` DATE NOT NULL,
    `status` ENUM('Pending', 'Confirmed', 'Cleared', 'Cancelled') DEFAULT 'Pending',
    `description` TEXT,
    `recipient_name` VARCHAR(255),
    `recipient_account` VARCHAR(100),
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `payment_type` (`payment_type`),
    INDEX `payment_date` (`payment_date`),
    INDEX `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses Table
CREATE TABLE IF NOT EXISTS `expenses` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `category` VARCHAR(100) NOT NULL, -- Equipment, Uniforms, Training, Maintenance, etc.
    `description` TEXT NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `currency` VARCHAR(3) DEFAULT 'USD',
    `expense_date` DATE NOT NULL,
    `payment_method` ENUM('Cash', 'Bank Transfer', 'Check', 'Credit Card') DEFAULT 'Cash',
    `status` ENUM('Approved', 'Pending', 'Rejected') DEFAULT 'Pending',
    `attachment` VARCHAR(255),
    `created_by` INT,
    `approved_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`approved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `category` (`category`),
    INDEX `expense_date` (`expense_date`),
    INDEX `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Messages Table
CREATE TABLE IF NOT EXISTS `messages` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `sender_id` INT NOT NULL,
    `recipient_id` INT NOT NULL,
    `subject` VARCHAR(255),
    `message` TEXT NOT NULL,
    `is_read` BOOLEAN DEFAULT FALSE,
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`recipient_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `sender_id` (`sender_id`),
    INDEX `recipient_id` (`recipient_id`),
    INDEX `is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications Table
CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `type` ENUM('info', 'success', 'warning', 'error', 'push') DEFAULT 'info',
    `link` VARCHAR(255),
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `user_id` (`user_id`),
    INDEX `read_at` (`read_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Log Table
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT,
    `action` VARCHAR(255) NOT NULL,
    `table_name` VARCHAR(100),
    `record_id` INT,
    `old_values` JSON,
    `new_values` JSON,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `user_id` (`user_id`),
    INDEX `table_name` (`table_name`),
    INDEX `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create default admin user (password: Admin@123)
INSERT INTO `users` (name, email, password, role, active, verified_at) 
VALUES ('Administrator', 'admin@ssci.local', '$2y$10$YIjlrBJVetp0.V5nP7RwUeD4XiBz2B9vbSXjCHV0k5RMHdU2v.nYK', 'admin', 1, NOW())
ON DUPLICATE KEY UPDATE id=id;

-- Create default settings
INSERT INTO `settings` (site_name, site_acronym, primary_color) 
VALUES ('SSCI Office', 'SSCI', '#2563eb')
ON DUPLICATE KEY UPDATE id=id;
