-- Jan 7 2026 added
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES
(1, 'user', 'face_registration_created_at', 'Face Registration Created On'),
(1, 'user', 'face_registration_registered', 'Face Registered'),
(1, 'user', 'face_registration_updated_at', 'Face Registration Updated On'),
(1, 'user', 'clear_face_registration_confirm', 'Are you sure you want to clear the face registration?');

-- Dec 29 2025 added
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES
(1, 'user', 'face_registration_status', 'Face Registration Status'),
(1, 'user', 'face_registration_not_registered', 'Face Not Registered'),
(1, 'user', 'face_registration_notice', 'Please register your face for biometric verification'),
(1, 'user', 'biometric', 'Biometric'),
(1, 'user', 'clear_face_registration', 'Clear Face Registration'),
(1, 'user', 'face_registration_refresh', 'Refresh Face Registration');
-- Dec 18 2025 added 
CREATE TABLE `self_configurations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` BIGINT UNSIGNED DEFAULT NULL,
  `group_name` VARCHAR(255) NOT NULL COMMENT 'payroll, hr, attendance',
  `config_key` VARCHAR(255) NOT NULL COMMENT 'has_letter_heads',
  `config_value` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = Off, 1 = On',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_self_configuration` (`company_id`, `group_name`, `config_key`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES 
(1,'common','select_all_users',"Select All Users"),(1,'menu','self_preferences','Self Configurations');

-- Dec 10 2025 Vendor related table
CREATE TABLE `vendors` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_id` BIGINT UNSIGNED DEFAULT NULL,
  `agency_name` varchar(255) NOT NULL,
  `agency_code` varchar(64) NOT NULL,
  `contact_person` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `alternate_phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `pincode` varchar(255) DEFAULT NULL,
  `gst_number` varchar(255) DEFAULT NULL,
  `pan_number` varchar(255) DEFAULT NULL,
  `service_categories` json DEFAULT NULL,
  `rating` decimal(2,1)  DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendors_email_unique` (`email`)
  -- FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Assumes a 'companies' table with an 'id' column exists
ALTER TABLE vendors 
ADD CONSTRAINT fk_vendors_company_id
FOREIGN KEY (company_id) 
REFERENCES companies (id) 
ON DELETE CASCADE; -- Or SET NULL, or RESTRICT, depending on your business rules

ALTER TABLE `users`
ADD COLUMN `vendor_id` BIGINT UNSIGNED NULL AFTER `weekoff_rule_id`,
ADD CONSTRAINT `users_vendor_id_foreign`
    FOREIGN KEY (`vendor_id`)
    REFERENCES `vendors`(`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;
--  sOptional old admins to update
INSERT INTO `permissions` ( `name`, `display_name`, `description`, `module_name`, `created_at`, `updated_at`) VALUES
( 'vendors_view', 'Vendors View', NULL, NULL, '2025-08-09 22:45:55', '2025-08-09 22:45:55'),
( 'vendors_create', 'Vendors Create', NULL, NULL, '2025-08-09 22:45:55', '2025-08-09 22:45:55'),
('vendors_edit', 'Vendors Edit', NULL, NULL, '2025-08-09 22:45:55', '2025-08-09 22:45:55'),
( 'vendors_delete', 'Vendors Delete', NULL, NULL, '2025-08-09 22:45:55', '2025-08-09 22:45:55');
-- insert into `permission_role`(`permission_id`,`role_id`) VALUES
-- (182,5),
-- (183,5),
-- (184,5),
-- (185,5);

ALTER TABLE companies
ADD COLUMN enable_vendor TINYINT(1) NOT NULL DEFAULT 0;


INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1,'common','agency_code','Shortcode-VEN-001'),
(1,'vendor','agency_code','Agency Code'),
(1,'vendor','enable_vendor','Enable Vendors'),
(1,'vendors','delete_message', 'Are you sure you want to delete this vendor?'),
(1,'vendors','selected_delete_message', 'Are you sure you want to delete selected vendors?'),
(1,'menu','vendors','Vendors'),
(1,'vendor','vendor','Vendor'),
(1,'vendor','vendors','Vendors'),
(1,'vendors','add','Add Vendor'),
(1,'vendors','edit','Edit Vendor'),
(1,'vendor','created','Vendor Created Successfully'),
(1,'vendors','updated','Vendor Updated Successfully'),
(1,'vendor','deleted','Vendor Deleted Successfully'),
(1,'vendor','agency_name','Agency Name'),
(1,'vendor','contact_person','Contact Person'),
(1,'vendor','email','Email'),
(1,'vendor','phone','Phone'),
(1,'vendor','alternate_phone','Alternate Phone'),
(1,'vendor','address','Address'),
(1,'vendor','city','City'),
(1,'vendor','state','State'),
(1,'vendor','pincode','Pincode'),
(1,'vendor','gst_number','GST Number'),
(1,'vendor','pan_number','PAN Number'),
(1,'vendor','service_categories','Service Categories'),
(1,'vendor','rating','Rating'),
(1,'vendor','status','Status'),
(1,'vendor','active','Active'),
(1,'vendor','inactive','Inactive'),
(1,'vendor','details','Vendor Details'),
(1,'vendor','list','Vendor List'),
(1,'vendor','create','Create Vendor'),
(1,'vendor','update','Update Vendor');


-- Dec 5 2025 Income Tax Related settings are added
-- SAAS Indian Income Tax System SQL Schema with Seed Data
CREATE TABLE income_tax_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name VARCHAR(100) NOT NULL,
    plan_code VARCHAR(50) NOT NULL,
    allow_deductions BOOLEAN DEFAULT TRUE,
    standard_deduction DECIMAL(10,2) DEFAULT 50000,
    description TEXT,
    effective_from DATE,
    effective_to DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tax_slabs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    slab_no INT,
    min_amount DECIMAL(12,2) NOT NULL,
    max_amount DECIMAL(12,2),
    tax_rate DECIMAL(5,2) NOT NULL,
    cess_rate DECIMAL(5,2) DEFAULT 4.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES income_tax_plans(id)
);

CREATE TABLE deduction_sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_code VARCHAR(20),
    section_name VARCHAR(100),
    limit_amount DECIMAL(12,2),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES
(1,'incomeTax','slab','Slab'),
(1,'incomeTax','collapse_all','Collapse All'),
(1,'incomeTax','expand_all','Expand All'),
(1,'incomeTax','tax_preview','Tax Slab Summary'),
(1,'incomeTax','apply_old_regime','Old Regime'),
(1,'incomeTax','apply_new_regime','New Regime'),
(1,'incomeTax','and_above','and above'),
(1, 'incomeTax', 'save_success', 'Saved plan successfully.'),
(1, 'incomeTax', 'edit_plan', 'Edit Plan'),
(1, 'incomeTax', 'tax_slabs', 'Tax Slabs'),
(1, 'incomeTax', 'add_slab', 'Add Tax Slab'),
(1, 'incomeTax', 'slab_no', 'No'),
(1, 'incomeTax', 'min_amount', 'Min Amount'),
(1, 'incomeTax', 'max_amount', 'Max Amount'),
(1, 'incomeTax', 'tax_rate', 'Tax Rate'),
(1, 'incomeTax', 'percent_symbol', '%'),
(1, 'incomeTax', 'is_active', 'Active'),
(1, 'incomeTax', 'description', 'Description'),
(1, 'incomeTax', 'add_plan', 'Add Plan'),
(1, 'menu', 'incometaxsettings', 'Income Tax Settings'),
(1, 'incomeTax', 'plan_name', 'Plan Name'),
(1, 'incomeTax', 'plan_code', 'Plan Code'),
(1, 'incomeTax', 'allow_deductions', 'Allow Deductions'),
(1, 'incomeTax', 'standard_deduction', 'Standard Deduction'),
(1, 'incomeTax', 'effective_from', 'Effective From'),
(1, 'incomeTax', 'effective_to', 'Effective To'),
(1, 'incomeTax', 'tax_slabs', 'Tax Slabs'),
(1, 'incomeTax', 'add_slab', 'Add Slab'),
(1, 'incomeTax', 'slab_no', 'Slab #'),
(1, 'incomeTax', 'min_amount', 'Min Amount'),
(1, 'incomeTax', 'max_amount', 'Max Amount'),
(1, 'incomeTax', 'tax_rate', 'Tax Rate (%)'),
(1, 'incomeTax', 'cess_rate', 'Cess Rate (%)'),
(1, 'incomeTax', 'percent_symbol', '%'),
(1, 'incomeTax', 'deduction_sections', 'Deduction Sections'),
(1, 'incomeTax', 'section_code', 'Section Code'),
(1, 'incomeTax', 'section_name', 'Section Name'),
(1, 'incomeTax', 'limit_amount', 'Limit Amount'),
(1, 'incomeTax', 'add_deduction_section', 'Add Deduction Section'),
(1, 'incomeTax', 'edit_deduction_section', 'Edit Deduction Section'),
(1, 'incomeTax', 'save_deduction_section_success', 'Saved deduction section successfully.'),
(1, 'menu', 'deduction_sections', 'Deduction Sections');


-- 29-11-2025 Durgaprasad payroll_settings changes start here
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'user', 'employee_id_msg', 'Employee ID will be auto-generated if not entered.');

CREATE TABLE IF NOT EXISTS `payroll_settings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint UNSIGNED DEFAULT NULL,
  `has_pf` tinyint(1) NOT NULL DEFAULT '0',
  `pf_contribution` varchar(255) NOT NULL DEFAULT 'basic_percent_special_allowence',
  `pf_ceiling_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `has_esi` tinyint(1) NOT NULL DEFAULT '0',
  `deduct_pt` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payroll_settings_company_id_index` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 29-11-2025 Durgaprasad changes end here

-- 24-11-2025 Durgaprasad changes updated
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'salary_group', 'add_earning_component', 'Add Earning Component');
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'salary_group', 'add_deduction_component', 'Add Deduction Component');
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'salary_group', 'salary_structure', 'Salary Structure');
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'salary_group', 'assign_users', 'Assign Users');
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'salary_group', 'view_mode_message', 'This salary structure form is currently in view-only mode.');
INSERT INTO `permissions` (`id`, `name`, `display_name`, `description`, `module_name`) VALUES (NULL, 'payroll_settings_view', 'Payroll Settings', NULL, NULL);
-- changes end here

-- Manasa added translations on  Nov 19 2025
CREATE TABLE `weekoff_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `effective_from` DATE NOT NULL,
  `pattern` JSON NULL,
  `is_company_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `updated_by` BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `weekoff_rules_company_id_foreign`
    FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `weekoff_rules_created_by_foreign`
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT `weekoff_rules_updated_by_foreign`
    FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users`
ADD COLUMN `weekoff_rule_id` BIGINT UNSIGNED NULL AFTER `shift_id`,
ADD CONSTRAINT `users_weekoff_rule_id_foreign`
    FOREIGN KEY (`weekoff_rule_id`)
    REFERENCES `weekoff_rules`(`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

CREATE TABLE `employee_weekoff_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint unsigned NOT NULL,
  `weekoff_rule_id` bigint unsigned DEFAULT NULL,
  `effective_date` date NOT NULL,
  `assigned_by` bigint unsigned DEFAULT NULL,
  `remark` text DEFAULT NULL,
  `company_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_weekoff_history_employee_id_index` (`employee_id`),
  KEY `employee_weekoff_history_weekoff_rule_id_index` (`weekoff_rule_id`),
  KEY `employee_weekoff_history_effective_date_index` (`effective_date`),
  KEY `employee_weekoff_history_company_id_index` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- UPDATE users u
-- JOIN (
--     SELECT ewr.user_id, ewr.weekoff_rule_id
--     FROM employee_weekoff_rules ewr
--     JOIN (
--         SELECT user_id, MAX(effective_from) AS effective_from
--         FROM employee_weekoff_rules
--         WHERE effective_to IS NULL
--         GROUP BY user_id
--     ) latest
--         ON latest.user_id = ewr.user_id
--        AND latest.effective_from = ewr.effective_from
-- ) active_assignment ON active_assignment.user_id = u.id
-- SET u.weekoff_rule_id = active_assignment.weekoff_rule_id
-- WHERE u.weekoff_rule_id IS NULL;

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'weekoff_rules', 'Weekoff Rules');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'employee_weekoff_rules', 'Assign Rules');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'assign', 'Assign');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'history', 'History');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'effective_from', 'Effective From');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'default', 'Default');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'weekoff_pattern', 'Weekoff Pattern');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'set_company_default', 'Set Company Default');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'please_enter_name', 'Please Enter Name');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'half_day', 'Half Day');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'weekoff', 'Weekoff');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'set_default', 'Default Rule');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'default_rule_set', 'Default Rule Set');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'error_setting_default', 'Error Setting Default');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'weekoff_rule', 'Weekoff Rule');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'employee', 'Employee');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'common', 'not_assigned', 'Not Assigned Rule');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'attendance', 'week_off', 'Week Off');
-- Ended 

-- v1.1 changes started
insert INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES ('1', 'menu', 'about', 'About');
-- v1.1 changes ended

