-- FinalTech HRMS Database Schema
-- Compatible with MySQL 8.0+
-- Optimized for cPanel hosting environments

START TRANSACTION;

-- --------------------------------------------------------
-- Table structure for table `branches`
-- --------------------------------------------------------

CREATE TABLE `branches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` text,
  `phone` varchar(20),
  `email` varchar(100),
  `manager` varchar(100),
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `departments`
-- --------------------------------------------------------

CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `branchId` int(11) NOT NULL,
  `headOfDepartment` varchar(100),
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `branchId` (`branchId`),
  CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`branchId`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `occupations`
-- --------------------------------------------------------

CREATE TABLE `occupations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text,
  `baseSalary` decimal(10,2) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `attendance`
-- --------------------------------------------------------

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeId` int(11) NOT NULL,
  `date` date NOT NULL,
  `timeIn` time DEFAULT NULL,
  `timeOut` time DEFAULT NULL,
  `hoursWorked` decimal(4,2) DEFAULT NULL,
  `status` enum('present','absent','late','half_day','overtime') NOT NULL DEFAULT 'present',
  `notes` text,
  `createdBy` varchar(255),
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_date` (`employeeId`, `date`),
  KEY `employeeId` (`employeeId`),
  KEY `date` (`date`),
  CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `payroll`
-- --------------------------------------------------------

CREATE TABLE `payroll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeId` int(11) NOT NULL,
  `payPeriodStart` date NOT NULL,
  `payPeriodEnd` date NOT NULL,
  `baseSalary` decimal(10,2) NOT NULL,
  `allowances` decimal(8,2) DEFAULT 0,
  `overtime` decimal(8,2) DEFAULT 0,
  `grossPay` decimal(10,2) NOT NULL,
  `taxDeduction` decimal(8,2) DEFAULT 0,
  `socialSecurityDeduction` decimal(8,2) DEFAULT 0,
  `loanDeduction` decimal(8,2) DEFAULT 0,
  `advanceDeduction` decimal(8,2) DEFAULT 0,
  `otherDeductions` decimal(8,2) DEFAULT 0,
  `totalDeductions` decimal(8,2) NOT NULL,
  `netPay` decimal(10,2) NOT NULL,
  `paymentStatus` enum('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  `paymentDate` date DEFAULT NULL,
  `paymentMethod` varchar(50) DEFAULT 'bank_transfer',
  `notes` text,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  KEY `payPeriodStart` (`payPeriodStart`),
  KEY `payPeriodEnd` (`payPeriodEnd`),
  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `loans`
-- --------------------------------------------------------

CREATE TABLE `loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeId` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `interestRate` decimal(5,2) DEFAULT 0,
  `installmentAmount` decimal(8,2) NOT NULL,
  `remainingBalance` decimal(10,2) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `purpose` varchar(255),
  `approvedBy` varchar(255),
  `notes` text,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `salary_advances`
-- --------------------------------------------------------

CREATE TABLE `salary_advances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeId` int(11) NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `requestDate` date NOT NULL,
  `approvalDate` date DEFAULT NULL,
  `status` enum('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `repaymentDate` date,
  `reason` text,
  `approvedBy` varchar(255),
  `notes` text,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  CONSTRAINT `salary_advances_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `deductions`
-- --------------------------------------------------------

CREATE TABLE `deductions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeId` int(11) NOT NULL,
  `type` enum('tax','loan','advance','disciplinary','other') NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `appliedToPayroll` tinyint(1) NOT NULL DEFAULT 0,
  `payrollId` int(11) DEFAULT NULL,
  `approvedBy` varchar(255),
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employeeId` (`employeeId`),
  KEY `payrollId` (`payrollId`),
  CONSTRAINT `deductions_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `deductions_ibfk_2` FOREIGN KEY (`payrollId`) REFERENCES `payroll` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `company_settings`
-- --------------------------------------------------------

CREATE TABLE `company_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyName` varchar(200) NOT NULL,
  `address` text,
  `phone` varchar(20),
  `email` varchar(100),
  `website` varchar(100),
  `logo` varchar(255),
  `taxRate` decimal(5,2) DEFAULT 0,
  `socialSecurityRate` decimal(5,2) DEFAULT 0,
  `workingHours` int(11) DEFAULT 8,
  `payrollFrequency` enum('weekly','biweekly','monthly') DEFAULT 'monthly',
  `fiscalYearStart` date,
  `timezone` varchar(50) DEFAULT 'UTC',
  `currency` varchar(10) DEFAULT 'USD',
  `dateFormat` varchar(20) DEFAULT 'YYYY-MM-DD',
  `backupEnabled` tinyint(1) DEFAULT 1,
  `backupFrequency` enum('daily','weekly','monthly') DEFAULT 'weekly',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `audit_logs`
-- --------------------------------------------------------

CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255),
  `action` varchar(100) NOT NULL,
  `tableName` varchar(50),
  `recordId` varchar(50),
  `oldValues` text,
  `newValues` text,
  `ipAddress` varchar(45),
  `userAgent` text,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `action` (`action`),
  KEY `tableName` (`tableName`),
  KEY `createdAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Create indexes for better performance
-- --------------------------------------------------------

CREATE INDEX idx_attendance_employee_date ON attendance(employeeId, date);
CREATE INDEX idx_payroll_employee_period ON payroll(employeeId, payPeriodStart, payPeriodEnd);
CREATE INDEX idx_loans_status ON loans(status);
CREATE INDEX idx_salary_advances_status ON salary_advances(status);
CREATE INDEX idx_deductions_status ON deductions(status);

COMMIT;

-- --------------------------------------------------------
-- Insert default company settings
-- --------------------------------------------------------

INSERT INTO `company_settings` (`companyName`, `address`, `phone`, `email`, `workingHours`, `payrollFrequency`, `currency`) VALUES
('FinalTech HRMS', '123 Business Street, City, State 12345', '+1-555-0123', 'info@company.com', 8, 'monthly', 'USD');

-- --------------------------------------------------------
-- Insert default branch
-- --------------------------------------------------------

INSERT INTO `branches` (`name`, `address`, `phone`, `email`, `manager`) VALUES
('Main Office', '123 Business Street, City, State 12345', '+1-555-0123', 'mainoffice@company.com', 'Admin Manager');

-- --------------------------------------------------------
-- Insert default department
-- --------------------------------------------------------

INSERT INTO `departments` (`name`, `description`, `branchId`, `headOfDepartment`) VALUES
('Administration', 'Main administrative department', 1, 'Admin Manager');

-- --------------------------------------------------------
-- Insert default occupation
-- --------------------------------------------------------

INSERT INTO `occupations` (`title`, `description`, `baseSalary`) VALUES
('Administrator', 'System administrator and manager', 5000.00);