-- =========================================================
-- LEAD/SALES/SERVICE MANAGEMENT SYSTEM + HR MODULE
-- MySQL 5.7/8 compatible, PHP 7.3 backend
-- =========================================================

-- On shared hosting the database is usually pre-created via cPanel.
-- Import this file directly into that database via phpMyAdmin (no CREATE DATABASE/USE needed).

-- =========================================================
-- 1. ORGANIZATION STRUCTURE
-- =========================================================

CREATE TABLE organizations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('sales','service','admin','hr','finance','other') NOT NULL DEFAULT 'other',
    parent_department_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organizations(id),
    FOREIGN KEY (parent_department_id) REFERENCES departments(id)
);

CREATE TABLE designations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    level INT DEFAULT 0,            -- e.g. 1=Owner,2=Dept Head,3=Team Lead,4=Agent
    department_id INT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- =========================================================
-- 2. EMPLOYEES / USERS (Login + HR Profile combined)
-- =========================================================

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org_id INT NOT NULL,
    employee_code VARCHAR(30) UNIQUE NOT NULL,

    -- Personal Info
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(20),
    gender ENUM('male','female','other'),
    dob DATE,
    photo_path VARCHAR(255),

    -- Address
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),

    -- Identity / Compliance
    aadhaar_no VARCHAR(20),
    pan_no VARCHAR(20),
    bank_account_no VARCHAR(30),
    bank_ifsc VARCHAR(20),
    bank_name VARCHAR(100),

    -- Employment Info
    department_id INT NOT NULL,
    designation_id INT,
    role ENUM('owner','dept_head','team_lead','agent','hr','accountant') NOT NULL,
    reporting_to INT NULL,              -- self-referencing: team_lead -> dept_head -> owner
    date_of_joining DATE NOT NULL,
    date_of_leaving DATE NULL,
    employment_status ENUM('active','inactive','terminated','resigned','on_notice') DEFAULT 'active',
    employment_type ENUM('full_time','part_time','contract','intern') DEFAULT 'full_time',

    -- Auth
    password_hash VARCHAR(255) NOT NULL,
    last_login DATETIME,
    is_login_active TINYINT(1) DEFAULT 1,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (org_id) REFERENCES organizations(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (designation_id) REFERENCES designations(id),
    FOREIGN KEY (reporting_to) REFERENCES employees(id),
    INDEX idx_dept_role (department_id, role),
    INDEX idx_city_state (city, state),
    INDEX idx_reporting (reporting_to)
);

-- Team grouping (a team belongs to a team lead under a department)
CREATE TABLE teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_id INT NOT NULL,
    team_lead_id INT NOT NULL,
    team_name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (team_lead_id) REFERENCES employees(id)
);

CREATE TABLE team_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_id INT NOT NULL,
    employee_id INT NOT NULL,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY uniq_team_emp (team_id, employee_id)
);

-- =========================================================
-- 3. HR - SALARY STRUCTURE
-- =========================================================

CREATE TABLE salary_structures (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE NULL,           -- null = currently active

    basic DECIMAL(10,2) DEFAULT 0,
    hra DECIMAL(10,2) DEFAULT 0,
    conveyance_allowance DECIMAL(10,2) DEFAULT 0,
    medical_allowance DECIMAL(10,2) DEFAULT 0,
    special_allowance DECIMAL(10,2) DEFAULT 0,
    other_allowance DECIMAL(10,2) DEFAULT 0,

    pf_employee DECIMAL(10,2) DEFAULT 0,
    pf_employer DECIMAL(10,2) DEFAULT 0,
    esi_employee DECIMAL(10,2) DEFAULT 0,
    esi_employer DECIMAL(10,2) DEFAULT 0,
    professional_tax DECIMAL(10,2) DEFAULT 0,
    tds DECIMAL(10,2) DEFAULT 0,

    gross_salary DECIMAL(10,2) GENERATED ALWAYS AS
        (basic + hra + conveyance_allowance + medical_allowance + special_allowance + other_allowance) STORED,
    total_deductions DECIMAL(10,2) GENERATED ALWAYS AS
        (pf_employee + esi_employee + professional_tax + tds) STORED,
    net_salary DECIMAL(10,2) GENERATED ALWAYS AS
        (basic + hra + conveyance_allowance + medical_allowance + special_allowance + other_allowance
         - pf_employee - esi_employee - professional_tax - tds) STORED,

    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (created_by) REFERENCES employees(id),
    INDEX idx_emp_effective (employee_id, effective_from)
);

-- =========================================================
-- 4. HR - PAYROLL / PAYSLIPS (monthly run)
-- =========================================================

CREATE TABLE payroll_runs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org_id INT NOT NULL,
    pay_month TINYINT NOT NULL,       -- 1-12
    pay_year SMALLINT NOT NULL,
    status ENUM('draft','processed','paid','locked') DEFAULT 'draft',
    processed_by INT,
    processed_at DATETIME,
    FOREIGN KEY (org_id) REFERENCES organizations(id),
    FOREIGN KEY (processed_by) REFERENCES employees(id),
    UNIQUE KEY uniq_org_month_year (org_id, pay_month, pay_year)
);

CREATE TABLE payslips (
    id INT PRIMARY KEY AUTO_INCREMENT,
    payroll_run_id INT NOT NULL,
    employee_id INT NOT NULL,

    -- snapshot of salary at time of generation
    basic DECIMAL(10,2) DEFAULT 0,
    hra DECIMAL(10,2) DEFAULT 0,
    conveyance_allowance DECIMAL(10,2) DEFAULT 0,
    medical_allowance DECIMAL(10,2) DEFAULT 0,
    special_allowance DECIMAL(10,2) DEFAULT 0,
    other_allowance DECIMAL(10,2) DEFAULT 0,
    incentive DECIMAL(10,2) DEFAULT 0,     -- variable, from incentives table
    bonus DECIMAL(10,2) DEFAULT 0,

    pf_employee DECIMAL(10,2) DEFAULT 0,
    esi_employee DECIMAL(10,2) DEFAULT 0,
    professional_tax DECIMAL(10,2) DEFAULT 0,
    tds DECIMAL(10,2) DEFAULT 0,
    loan_deduction DECIMAL(10,2) DEFAULT 0,
    lop_deduction DECIMAL(10,2) DEFAULT 0,  -- loss of pay for unpaid leaves

    days_present DECIMAL(5,2) DEFAULT 0,
    days_in_month TINYINT DEFAULT 30,
    paid_leaves DECIMAL(5,2) DEFAULT 0,
    unpaid_leaves DECIMAL(5,2) DEFAULT 0,

    gross_earnings DECIMAL(10,2) DEFAULT 0,
    total_deductions DECIMAL(10,2) DEFAULT 0,
    net_pay DECIMAL(10,2) DEFAULT 0,

    payment_status ENUM('pending','paid','hold') DEFAULT 'pending',
    payment_date DATE NULL,
    remarks TEXT,

    FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY uniq_run_emp (payroll_run_id, employee_id)
);

-- =========================================================
-- 5. HR - LEAVE MANAGEMENT
-- =========================================================

CREATE TABLE leave_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,         -- Casual, Sick, Earned, Maternity, etc.
    is_paid TINYINT(1) DEFAULT 1,
    default_annual_quota DECIMAL(5,2) DEFAULT 0,
    carry_forward TINYINT(1) DEFAULT 0,
    max_carry_forward DECIMAL(5,2) DEFAULT 0
);

CREATE TABLE leave_balances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    year SMALLINT NOT NULL,
    allotted DECIMAL(5,2) DEFAULT 0,
    used DECIMAL(5,2) DEFAULT 0,
    carried_forward DECIMAL(5,2) DEFAULT 0,
    balance DECIMAL(5,2) GENERATED ALWAYS AS (allotted + carried_forward - used) STORED,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id),
    UNIQUE KEY uniq_emp_leave_year (employee_id, leave_type_id, year)
);

CREATE TABLE leave_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    leave_type_id INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    total_days DECIMAL(5,2) NOT NULL,
    half_day TINYINT(1) DEFAULT 0,
    reason TEXT,
    status ENUM('pending','approved','rejected','cancelled') DEFAULT 'pending',
    applied_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_by INT NULL,
    approved_at DATETIME NULL,
    rejection_reason TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (leave_type_id) REFERENCES leave_types(id),
    FOREIGN KEY (approved_by) REFERENCES employees(id),
    INDEX idx_emp_status (employee_id, status)
);

-- =========================================================
-- 6. HR - ATTENDANCE
-- =========================================================

CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    check_in DATETIME NULL,
    check_out DATETIME NULL,
    status ENUM('present','absent','half_day','on_leave','holiday','week_off','wfh') DEFAULT 'present',
    working_hours DECIMAL(5,2) DEFAULT 0,
    late_minutes INT DEFAULT 0,
    remarks VARCHAR(255),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY uniq_emp_date (employee_id, attendance_date),
    INDEX idx_date (attendance_date)
);

CREATE TABLE holidays (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org_id INT NOT NULL,
    holiday_date DATE NOT NULL,
    name VARCHAR(100),
    applicable_state VARCHAR(100) NULL,   -- NULL = all states
    FOREIGN KEY (org_id) REFERENCES organizations(id)
);

-- =========================================================
-- 7. HR - INCENTIVES / TARGETS (links to sales/service performance)
-- =========================================================

CREATE TABLE incentive_rules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_id INT NOT NULL,
    name VARCHAR(100),
    metric ENUM('installation_count','business_value','confirmed_count') NOT NULL,
    threshold_value DECIMAL(12,2) NOT NULL,
    incentive_amount DECIMAL(10,2) NOT NULL,
    effective_from DATE,
    effective_to DATE NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE employee_incentives (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    incentive_rule_id INT NULL,
    pay_month TINYINT NOT NULL,
    pay_year SMALLINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    reason VARCHAR(255),
    approved_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (incentive_rule_id) REFERENCES incentive_rules(id),
    FOREIGN KEY (approved_by) REFERENCES employees(id)
);

-- =========================================================
-- 8. HR - DOCUMENTS
-- =========================================================

CREATE TABLE employee_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    doc_type ENUM('aadhaar','pan','resume','offer_letter','experience_letter','photo','bank_proof','other') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- =========================================================
-- 9. LEAD MANAGEMENT (Sales + Service flow)
-- =========================================================

CREATE TABLE leads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    org_id INT NOT NULL,
    source ENUM('meta','google','website','referral','manual','other') DEFAULT 'meta',
    source_ref_id VARCHAR(100),         -- meta lead id / campaign id

    customer_name VARCHAR(150),
    phone VARCHAR(20),
    email VARCHAR(150),
    city VARCHAR(100),
    state VARCHAR(100),
    address TEXT,

    department_id INT NOT NULL,         -- currently owning department
    assigned_team_id INT NULL,
    assigned_employee_id INT NULL,

    status ENUM('new','not_contacted','called','in_pipeline','confirmed',
                 'waiting_install','installed','rejected') DEFAULT 'new',

    business_value DECIMAL(12,2) DEFAULT 0,
    product_interest VARCHAR(150),

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (org_id) REFERENCES organizations(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (assigned_team_id) REFERENCES teams(id),
    FOREIGN KEY (assigned_employee_id) REFERENCES employees(id),
    INDEX idx_status (status),
    INDEX idx_city_state (city, state),
    INDEX idx_created (created_at),
    INDEX idx_dept_status (department_id, status)
);

CREATE TABLE lead_status_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lead_id INT NOT NULL,
    old_status VARCHAR(30),
    new_status VARCHAR(30),
    changed_by INT NOT NULL,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT,
    FOREIGN KEY (lead_id) REFERENCES leads(id),
    FOREIGN KEY (changed_by) REFERENCES employees(id),
    INDEX idx_lead (lead_id)
);

CREATE TABLE call_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lead_id INT NOT NULL,
    employee_id INT NOT NULL,
    call_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    duration_seconds INT DEFAULT 0,
    outcome ENUM('connected','not_reachable','busy','wrong_number','interested','not_interested') DEFAULT 'connected',
    notes TEXT,
    FOREIGN KEY (lead_id) REFERENCES leads(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    INDEX idx_lead (lead_id)
);

-- Service zone mapping for auto-assigning installation leads
CREATE TABLE service_zone_mapping (
    id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(100),
    state VARCHAR(100),
    service_team_id INT NOT NULL,
    FOREIGN KEY (service_team_id) REFERENCES teams(id),
    INDEX idx_city_state (city, state)
);

CREATE TABLE installations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    lead_id INT NOT NULL,
    service_team_id INT NULL,
    service_employee_id INT NULL,
    scheduled_date DATE NULL,
    installed_date DATE NULL,
    status ENUM('pending','scheduled','installed','cancelled') DEFAULT 'pending',
    final_business_value DECIMAL(12,2) DEFAULT 0,
    remarks TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES leads(id),
    FOREIGN KEY (service_team_id) REFERENCES teams(id),
    FOREIGN KEY (service_employee_id) REFERENCES employees(id)
);

-- =========================================================
-- 10. AGGREGATION TABLE (for fast dashboards)
-- =========================================================

CREATE TABLE daily_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stat_date DATE NOT NULL,
    department_id INT NOT NULL,
    team_id INT NULL,
    employee_id INT NULL,
    city VARCHAR(100),
    state VARCHAR(100),

    total_calls INT DEFAULT 0,
    not_contacted INT DEFAULT 0,
    in_pipeline INT DEFAULT 0,
    confirmed INT DEFAULT 0,
    waiting_install INT DEFAULT 0,
    installed INT DEFAULT 0,
    rejected INT DEFAULT 0,

    business_value DECIMAL(14,2) DEFAULT 0,

    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (team_id) REFERENCES teams(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY uniq_stat (stat_date, department_id, team_id, employee_id, city, state),
    INDEX idx_date (stat_date),
    INDEX idx_dept_date (department_id, stat_date)
);

-- =========================================================
-- 11. AUDIT / LOGIN LOGS
-- =========================================================

CREATE TABLE login_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    logout_time DATETIME NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- =========================================================
-- SEED DATA (basic)
-- =========================================================

INSERT INTO leave_types (name, is_paid, default_annual_quota, carry_forward, max_carry_forward) VALUES
('Casual Leave', 1, 12, 0, 0),
('Sick Leave', 1, 7, 0, 0),
('Earned Leave', 1, 15, 1, 30),
('Unpaid Leave', 0, 0, 0, 0);

INSERT INTO designations (title, level, department_id) VALUES
('Owner', 1, NULL),
('Department Head', 2, NULL),
('Team Lead', 3, NULL),
('Sales Executive', 4, NULL),
('Service Engineer', 4, NULL),
('HR Manager', 2, NULL);