-- Payroll Management Database Tables

-- Staff table to store employee information
CREATE TABLE `payroll_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` varchar(20) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `designation` varchar(100) NOT NULL,
  `employment_type` enum('FULL_TIME','PART_TIME','CONTRACT','INTERN') NOT NULL DEFAULT 'FULL_TIME',
  `basic_salary` decimal(12,2) NOT NULL,
  `allowances` decimal(12,2) DEFAULT 0.00,
  `deductions` decimal(12,2) DEFAULT 0.00,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `ifsc_code` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `joining_date` date NOT NULL,
  `status` enum('ACTIVE','INACTIVE','TERMINATED') DEFAULT 'ACTIVE',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp DEFAULT current_timestamp(),
  `updated_at` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `staff_id` (`staff_id`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_status` (`status`),
  KEY `idx_staff_id` (`staff_id`),
  CONSTRAINT `payroll_staff_ibfk_1` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE,
  CONSTRAINT `payroll_staff_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll batches table to track monthly payroll processing
CREATE TABLE `payroll_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(50) NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `total_staff` int(11) NOT NULL DEFAULT 0,
  `total_gross_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_deductions` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_net_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('DRAFT','GENERATED','VERIFIED','PAID') DEFAULT 'DRAFT',
  `generated_by` bigint(20) unsigned NOT NULL,
  `generated_date` datetime DEFAULT current_timestamp(),
  `verified_by` bigint(20) unsigned DEFAULT NULL,
  `verified_date` datetime DEFAULT NULL,
  `payment_verified_by` bigint(20) unsigned DEFAULT NULL,
  `payment_verified_date` datetime DEFAULT NULL,
  `created_at` timestamp DEFAULT current_timestamp(),
  `updated_at` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `batch_id` (`batch_id`),
  UNIQUE KEY `unique_month_year` (`month`, `year`),
  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_month_year` (`month`, `year`),
  KEY `idx_status` (`status`),
  CONSTRAINT `payroll_batches_ibfk_1` FOREIGN KEY (`generated_by`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `payroll_batches_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `Users` (`UserID`),
  CONSTRAINT `payroll_batches_ibfk_3` FOREIGN KEY (`payment_verified_by`) REFERENCES `Users` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Individual payroll entries for each staff member per month
CREATE TABLE `payroll_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(50) NOT NULL,
  `staff_id` varchar(20) NOT NULL,
  `chapter_id` bigint(20) unsigned NOT NULL,
  `staff_name` varchar(100) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `basic_salary` decimal(12,2) NOT NULL,
  `allowances` decimal(12,2) DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL,
  `tax_deduction` decimal(12,2) DEFAULT 0.00,
  `other_deductions` decimal(12,2) DEFAULT 0.00,
  `total_deductions` decimal(12,2) DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `ifsc_code` varchar(20) DEFAULT NULL,
  `payment_status` enum('PENDING','PROCESSED','PAID') DEFAULT 'PENDING',
  `payment_reference` varchar(100) DEFAULT NULL,
  `created_at` timestamp DEFAULT current_timestamp(),
  `updated_at` timestamp DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_batch_id` (`batch_id`),
  KEY `idx_staff_id` (`staff_id`),
  KEY `idx_chapter_id` (`chapter_id`),
  KEY `idx_payment_status` (`payment_status`),
  CONSTRAINT `payroll_entries_ibfk_1` FOREIGN KEY (`batch_id`) REFERENCES `payroll_batches` (`batch_id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_entries_ibfk_2` FOREIGN KEY (`staff_id`) REFERENCES `payroll_staff` (`staff_id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_entries_ibfk_3` FOREIGN KEY (`chapter_id`) REFERENCES `Chapters` (`ChapterID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert STAFF WAGES category into finance_expense_categories if it doesn't exist
INSERT IGNORE INTO finance_expense_categories (category_name, description)
VALUES ('STAFF WAGES', 'Staff salary and wage payments');
