USE hrms_payroll;

-- Role updates to ensure the correct 6 business roles exist
INSERT INTO roles (company_id, name, slug) VALUES 
(1, 'Super Admin', 'super_admin'),
(1, 'HR Admin', 'hr_admin'),
(1, 'Payroll Manager', 'payroll_manager'),
(1, 'Accounts Manager', 'accounts_manager'),
(1, 'Department Manager', 'department_manager'),
(1, 'Employee', 'employee')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 1. Salary Structure Master
CREATE TABLE IF NOT EXISTS salary_structures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    ot_policy_id BIGINT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    conveyance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    pf_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    esi_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    prof_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tds DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (ot_policy_id) REFERENCES ot_policies(id) ON DELETE SET NULL
);

-- 2. Employee Salary Assignment
CREATE TABLE IF NOT EXISTS employee_salary_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL UNIQUE,
    salary_structure_id BIGINT UNSIGNED NULL,
    effective_from DATE NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    conveyance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    pf_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    esi_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    prof_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tds DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (salary_structure_id) REFERENCES salary_structures(id) ON DELETE SET NULL
);

-- 3. Payroll Period
CREATE TABLE IF NOT EXISTS payroll_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL, -- e.g., "August 2026"
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('Draft', 'Processing', 'Processed', 'Approved', 'Locked') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- 4. Employee Payroll Records
CREATE TABLE IF NOT EXISTS employee_payroll_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    working_days INT UNSIGNED NOT NULL DEFAULT 0,
    present_days INT UNSIGNED NOT NULL DEFAULT 0,
    lwp_days DECIMAL(4,1) NOT NULL DEFAULT 0.0,
    overtime_hours DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    hra DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    conveyance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_allowances DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    overtime_earnings DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    pf_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    esi_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    prof_tax DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tds DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    lwp_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    loan_deduction DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    net_salary DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('Draft', 'Processed', 'Approved') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE KEY uq_period_employee (payroll_period_id, employee_id)
);

-- 5. Payslips
CREATE TABLE IF NOT EXISTS payslips (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_payroll_record_id BIGINT UNSIGNED NOT NULL UNIQUE,
    employee_id BIGINT UNSIGNED NOT NULL,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    payslip_number VARCHAR(100) NOT NULL UNIQUE,
    pdf_path VARCHAR(500) NULL,
    status ENUM('Pending', 'Released') DEFAULT 'Pending',
    released_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_payroll_record_id) REFERENCES employee_payroll_records(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
);

-- 6. Payroll Payment Batches
CREATE TABLE IF NOT EXISTS payroll_payment_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    payment_date DATE NULL,
    payment_reference VARCHAR(100) NULL,
    status ENUM('Pending', 'Paid', 'Failed', 'Cancelled') DEFAULT 'Pending',
    total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
);

-- 7. Employee Payroll Payments
CREATE TABLE IF NOT EXISTS employee_payroll_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_batch_id BIGINT UNSIGNED NOT NULL,
    employee_payroll_record_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    payment_status ENUM('Pending', 'Paid', 'Failed', 'Cancelled') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_batch_id) REFERENCES payroll_payment_batches(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_payroll_record_id) REFERENCES employee_payroll_records(id) ON DELETE CASCADE
);

-- 8. Payroll Journals
CREATE TABLE IF NOT EXISTS payroll_journals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    payroll_period_id BIGINT UNSIGNED NOT NULL,
    entry_date DATE NOT NULL,
    description VARCHAR(255) NOT NULL,
    total_debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    total_credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payroll_journal_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_journal_id BIGINT UNSIGNED NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    entry_type ENUM('Debit', 'Credit') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payroll_journal_id) REFERENCES payroll_journals(id) ON DELETE CASCADE
);

-- 9. Employee Advances / Loans
CREATE TABLE IF NOT EXISTS employee_loans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    loan_type ENUM('Advance', 'Loan') NOT NULL DEFAULT 'Advance',
    amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    installment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    outstanding_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('Pending', 'Approved', 'Rejected', 'Active', 'PaidOff') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS employee_loan_repayments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_loan_id BIGINT UNSIGNED NOT NULL,
    employee_payroll_record_id BIGINT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL,
    repayment_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_loan_id) REFERENCES employee_loans(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_payroll_record_id) REFERENCES employee_payroll_records(id) ON DELETE SET NULL
);

-- 10. Notifications
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'danger') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);