-- Database Schema for AUP HR & Payroll System

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+06:00";

-- 1. Roles Table
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `slug` varchar(50) NOT NULL UNIQUE,
  `permissions` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`name`, `slug`) VALUES
('Super Admin', 'super_admin'),
('HR Admin', 'hr_admin'),
('Executive Director', 'executive_director'),
('Deputy Executive Director', 'deputy_ed'),
('Director', 'director'),
('Area Manager', 'area_manager'),
('Branch Manager', 'branch_manager'),
('Employee', 'employee');

-- 2. Branches Table
CREATE TABLE `branches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) UNIQUE,
  `address` text,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Users Table (Login System)
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`),
  FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Employees Table (Profile)
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `employee_id` varchar(20) NOT NULL UNIQUE,
  `full_name` varchar(100) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `grade` varchar(20) DEFAULT NULL,
  `joining_date` date NOT NULL,
  `email` varchar(100),
  `phone` varchar(20),
  `nid` varchar(50),
  `address` text,
  `photo` varchar(255) DEFAULT NULL,
  `status` enum('Regular','Probation','Project','Terminated','Resigned') DEFAULT 'Probation',
  `basic_salary` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Payroll Master (Configuration)
CREATE TABLE `payroll_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_key` varchar(50) NOT NULL UNIQUE,
  `config_value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `payroll_config` (`config_key`, `config_value`) VALUES
('house_rent_percent', '75'),
('medical_allowance', '800'),
('conveyance_allowance', '600'),
('pf_employee_percent', '10'),
('pf_company_percent', '10');

-- 6. Payroll Transactions (Monthly)
CREATE TABLE `payroll_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `basic_salary` decimal(10,2) DEFAULT 0.00,
  `house_rent` decimal(10,2) DEFAULT 0.00,
  `medical` decimal(10,2) DEFAULT 0.00,
  `conveyance` decimal(10,2) DEFAULT 0.00,
  `special_allowance` decimal(10,2) DEFAULT 0.00,
  `variable_allowance` decimal(10,2) DEFAULT 0.00 COMMENT 'Sum of all variable inputs',
  `pf_deduction` decimal(10,2) DEFAULT 0.00,
  `other_deductions` decimal(10,2) DEFAULT 0.00,
  `net_pay` decimal(10,2) DEFAULT 0.00,
  `status` enum('Draft','Submitted','Approved','Paid') DEFAULT 'Draft',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
