ALTER TABLE `companies` 
    DROP COLUMN `google_geo_coding_api_key`,
    DROP COLUMN `export_pdf_left_space`,
    DROP COLUMN `export_pdf_right_space`,
    DROP COLUMN `export_pdf_top_space`,
    DROP COLUMN `export_pdf_bottom_space`,
    DROP COLUMN `export_pdf_line_height`,
    DROP COLUMN `export_pdf_font_size`;

ALTER TABLE `leave_types` 
    DROP COLUMN `count_type`;

ALTER TABLE `expense_categories` 
    DROP COLUMN `expense_for`;



ALTER TABLE `companies` 
    ADD `google_geo_coding_api_key` VARCHAR(255) NULL DEFAULT NULL,
    ADD `export_pdf_left_space` INT NULL DEFAULT 15,
    ADD `export_pdf_right_space` INT NULL DEFAULT 15,
    ADD `export_pdf_top_space` INT NULL DEFAULT 40,
    ADD `export_pdf_bottom_space` INT NULL DEFAULT 20,
    ADD `export_pdf_line_height` INT NULL DEFAULT 25,
    ADD `export_pdf_font_size` INT NULL DEFAULT 14;

UPDATE `users` 
SET `status` = 'active' 
WHERE `user_type` = 'staff_members' AND `status` = 'enabled';



CREATE TABLE `employee_specific_leave_counts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `user_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `leave_type_id` BIGINT UNSIGNED NULL DEFAULT NULL,
    `total_leaves` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    CONSTRAINT `employee_specific_leave_counts_company_id_foreign` 
        FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `employee_specific_leave_counts_user_id_foreign` 
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `employee_specific_leave_counts_leave_type_id_foreign` 
        FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types`(`id`) 
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `expense_categories` 
    ADD `expense_for` VARCHAR(25) NOT NULL DEFAULT 'all' AFTER `name`;

ALTER TABLE `leave_types` 
    ADD `count_type` VARCHAR(255) NOT NULL DEFAULT 'same_for_all';


-- 03-09-2025 db changes by krishnasai
CREATE TABLE IF NOT EXISTS `document_categories` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `document_categories`
--

INSERT INTO `document_categories` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Ids', NULL, NULL),
(2, 'certifications', NULL, NULL),
(3, 'work', NULL, NULL);
COMMIT;


CREATE TABLE IF NOT EXISTS `document_types` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `category_id` bigint NOT NULL,
  `name` varchar(150) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `document_types`
--

INSERT INTO `document_types` (`id`, `category_id`, `name`, `created_at`, `updated_at`) VALUES
(11, 1, 'Electricity Bill', NULL, NULL),
(10, 1, 'Voter ID', NULL, NULL),
(9, 1, 'Driving Licence', NULL, NULL),
(8, 1, 'Passport', NULL, NULL),
(7, 1, 'Aadhar Card', NULL, NULL),
(12, 1, 'Phone Bill', NULL, NULL),
(13, 1, 'Bank Passbook', NULL, NULL),
(14, 1, 'Rental Agreement', NULL, NULL),
(15, 2, 'Graduation', NULL, NULL),
(16, 2, 'Post Graduation', NULL, NULL),
(17, 2, 'Doctorate', NULL, NULL),
(18, 2, 'Diploma', NULL, NULL),
(19, 2, 'Pre-University', NULL, NULL),
(20, 2, 'Other', NULL, NULL),
(21, 3, 'General Work Document', NULL, NULL);
COMMIT;

CREATE TABLE IF NOT EXISTS `employee_documents` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `category_id` bigint NOT NULL,
  `document_type_id` bigint NOT NULL,
  `uploaded_by` varchar(255) NOT NULL,
  `document_id` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `verification` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `category_id` (`category_id`),
  KEY `document_type_id` (`document_type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'documents', 'Documents');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'document_type', 'Document Type');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'document_id', 'Document ID');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'uploaded_by', 'Uploaded By');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'user', 'verification', 'Verification');

INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'personal_info', 'Personal Info');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'company_relation', 'Company Relation');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'work_info', 'Work Info');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'bank_details', 'Bank Account Details');
INSERT INTO `translations` (`lang_id`, `group`, `key`, `value`) VALUES (1, 'menu', 'documents', 'Documents');


