-- Adds the full HR/Payroll module — employee salary structure, attendance
-- (incl. half-day/week-off/holiday/comp-off), leave types & requests, a
-- company holiday calendar, and payroll runs/payslips — to an already-
-- provisioned database. Only needed if your database predates this change —
-- a fresh `mysql -u root -p < schema.sql` import already includes all of it.
--
-- Usage: mysql -u root -p petzy_pos < add_hr_payroll.sql

-- 1) Employee salary structure + bank details -------------------------------
ALTER TABLE employees
    ADD COLUMN salary_type ENUM('monthly','daily','hourly') NOT NULL DEFAULT 'monthly' AFTER salary,
    ADD COLUMN hra DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER salary_type,
    ADD COLUMN other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER hra,
    ADD COLUMN week_off_day TINYINT UNSIGNED NULL COMMENT '0=Sunday .. 6=Saturday' AFTER other_allowances,
    ADD COLUMN bank_account_number VARCHAR(30) NULL AFTER week_off_day,
    ADD COLUMN bank_ifsc VARCHAR(15) NULL AFTER bank_account_number,
    ADD COLUMN bank_name VARCHAR(100) NULL AFTER bank_ifsc,
    ADD COLUMN pan_number VARCHAR(20) NULL AFTER bank_name;

-- 2) Attendance gains half_day/week_off/holiday/comp_worked ------------------
ALTER TABLE attendance
    MODIFY COLUMN status ENUM('present','late','half_day','absent','on_leave','week_off','holiday','comp_worked','overtime') NOT NULL DEFAULT 'present';

-- 3) Holiday calendar ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS holidays (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    holiday_date DATE NOT NULL,
    name VARCHAR(150) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_hol_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 4) Leave types + requests ----------------------------------------------------
CREATE TABLE IF NOT EXISTS leave_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    is_paid BOOLEAN NOT NULL DEFAULT 1,
    annual_quota DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_leavetype_company_code (company_id, code),
    CONSTRAINT fk_lt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leave_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NOT NULL,
    leave_type_id INT UNSIGNED NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    is_half_day BOOLEAN NOT NULL DEFAULT 0,
    day_count DECIMAL(5,1) NOT NULL DEFAULT 1.0,
    reason VARCHAR(255) NULL,
    status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lr_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_lr_leavetype FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB;

-- 5) Payroll runs + payslip items ---------------------------------------------
CREATE TABLE IF NOT EXISTS payroll_runs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    outlet_id INT UNSIGNED NULL,
    month TINYINT UNSIGNED NOT NULL,
    year SMALLINT UNSIGNED NOT NULL,
    status ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
    generated_by INT UNSIGNED NULL,
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalized_by INT UNSIGNED NULL,
    finalized_at DATETIME NULL,
    CONSTRAINT fk_pr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payroll_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT UNSIGNED NOT NULL,
    employee_id INT UNSIGNED NOT NULL,
    present_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    half_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    absent_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    paid_leave_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    unpaid_leave_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    week_off_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    holiday_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    comp_worked_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    payable_days DECIMAL(5,1) NOT NULL DEFAULT 0.0,
    total_days_in_period SMALLINT UNSIGNED NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    lop_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','paid') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    payment_method VARCHAR(30) NULL,
    payment_reference VARCHAR(100) NULL,
    expense_id INT UNSIGNED NULL,
    UNIQUE KEY uq_payitem_run_employee (payroll_run_id, employee_id),
    CONSTRAINT fk_pi_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_pi_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB;

-- 6) Permissions + grants ------------------------------------------------------
INSERT IGNORE INTO permissions (module, slug, description) VALUES
('payroll','payroll.view','View payroll'),
('payroll','payroll.manage','Manage payroll (attendance, leave approval, generate/finalize/pay runs)');

-- Any role that can already manage employees gets full payroll access too.
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT rp.role_id, p2.id
FROM role_permissions rp
JOIN permissions p1 ON p1.id = rp.permission_id AND p1.slug = 'employees.manage'
JOIN permissions p2 ON p2.slug IN ('payroll.view', 'payroll.manage');

-- Any role that can already view reports (finance/accountant-style roles)
-- gets read-only payroll visibility even without employees.manage.
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT rp.role_id, p2.id
FROM role_permissions rp
JOIN permissions p1 ON p1.id = rp.permission_id AND p1.slug = 'reports.view'
JOIN permissions p2 ON p2.slug = 'payroll.view';

-- 7) Default leave types per existing company (skips ones that already exist) -
INSERT IGNORE INTO leave_types (company_id, name, code, is_paid, annual_quota, status)
SELECT c.id, t.name, t.code, t.is_paid, t.annual_quota, 'active'
FROM companies c
CROSS JOIN (
    SELECT 'Casual Leave' AS name, 'CL' AS code, 1 AS is_paid, 12.0 AS annual_quota
    UNION ALL SELECT 'Sick Leave', 'SL', 1, 8.0
    UNION ALL SELECT 'Earned Leave', 'EL', 1, 15.0
    UNION ALL SELECT 'Comp Off', 'COMPOFF', 1, 0.0
    UNION ALL SELECT 'Leave Without Pay', 'LWP', 0, 0.0
) t;