CREATE DATABASE IF NOT EXISTS prime_plastics CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE prime_plastics;
CREATE TABLE employees (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, employment_type ENUM('Casual','Permanent') NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, deactivated_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE employee_mappings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, biotime_id VARCHAR(100) NULL UNIQUE, biotime_name VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(employee_id) REFERENCES employees(id));
CREATE TABLE daily_entries (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, work_date DATE NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, entry_type ENUM('Attendance','Leave') NOT NULL, shift ENUM('Day','Night') NULL, late TINYINT(1) NOT NULL DEFAULT 0, leave_type VARCHAR(100) NULL, sick_proof_status VARCHAR(50) NULL, normal_hours DECIMAL(8,2) NOT NULL DEFAULT 0, overtime_hours DECIMAL(8,2) NOT NULL DEFAULT 0, total_hours DECIMAL(8,2) NOT NULL DEFAULT 0, adjustment_hours DECIMAL(8,2) NOT NULL DEFAULT 0, adjustment_note TEXT NULL, approval_status ENUM('pending','approved','rectified') NOT NULL DEFAULT 'pending', approved_at DATETIME NULL, approved_by BIGINT UNSIGNED NULL, rectified_at DATETIME NULL, rectified_by BIGINT UNSIGNED NULL, rectification_note TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(employee_id) REFERENCES employees(id), INDEX(work_date), INDEX(employee_id,work_date));
CREATE TABLE payroll_employee_details (employee_id BIGINT UNSIGNED PRIMARY KEY, tin VARCHAR(100), nida VARCHAR(100), nssf_number VARCHAR(100), bank_name VARCHAR(255), account_number VARCHAR(100), updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(employee_id) REFERENCES employees(id));
CREATE TABLE employee_wages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, employment_type ENUM('Casual','Permanent') NOT NULL, daily_basic_rate DECIMAL(14,2) NULL, basic_salary DECIMAL(14,2) NULL, effective_from DATE NOT NULL, effective_to DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(employee_id) REFERENCES employees(id), INDEX(employee_id,effective_from));
CREATE TABLE payroll_settings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_group VARCHAR(100) NOT NULL, setting_key VARCHAR(100) NOT NULL, setting_value TEXT NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(setting_group,setting_key,effective_from));
CREATE TABLE paye_slabs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, minimum_income DECIMAL(14,2) NOT NULL, maximum_income DECIMAL(14,2) NULL, rate_percent DECIMAL(8,4) NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE payroll_periods (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, period_name VARCHAR(255) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, status ENUM('draft','locked','archived') NOT NULL DEFAULT 'draft', payroll_reference VARCHAR(100) NULL UNIQUE, approved_at DATETIME NULL, approved_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(from_date,to_date));
CREATE TABLE payroll_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payroll_period_id BIGINT UNSIGNED NOT NULL, employee_id BIGINT UNSIGNED NOT NULL, normal_hours DECIMAL(8,2) NOT NULL DEFAULT 0, overtime_hours DECIMAL(8,2) NOT NULL DEFAULT 0, gross_pay DECIMAL(14,2) NOT NULL DEFAULT 0, nssf DECIMAL(14,2) NOT NULL DEFAULT 0, paye DECIMAL(14,2) NOT NULL DEFAULT 0, advance DECIMAL(14,2) NOT NULL DEFAULT 0, manual_adjustment DECIMAL(14,2) NOT NULL DEFAULT 0, adjustment_reason TEXT NULL, net_pay DECIMAL(14,2) NOT NULL DEFAULT 0, calculation_snapshot JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id), FOREIGN KEY(employee_id) REFERENCES employees(id), UNIQUE(payroll_period_id,employee_id));
CREATE TABLE payroll_adjustments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payroll_item_id BIGINT UNSIGNED NOT NULL, previous_amount DECIMAL(14,2) NOT NULL, adjustment_amount DECIMAL(14,2) NOT NULL, new_amount DECIMAL(14,2) NOT NULL, reason TEXT NOT NULL, changed_by BIGINT UNSIGNED NULL, changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, reversed_at DATETIME NULL, reversed_by BIGINT UNSIGNED NULL, FOREIGN KEY(payroll_item_id) REFERENCES payroll_items(id));
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id BIGINT UNSIGNED NULL, details JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX(entity_type,entity_id));