-- Updated above all queries
ALTER TABLE `rooms` ADD `upload_documents` TEXT NULL DEFAULT NULL AFTER `organisation_id`;
ALTER TABLE `rooms` ADD `bed_id` SMALLINT(2) NOT NULL DEFAULT '0' AFTER `price`;
ALTER TABLE `rooms` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT, CHANGE `room_type` `room_type` TINYINT(2) NOT NULL DEFAULT '0', CHANGE `max_occupancy` `max_occupancy` TINYINT(2) NOT NULL DEFAULT '0', CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE `updated_at` `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `hotels` ADD `is_disabled` TINYINT(1) NOT NULL DEFAULT '0' AFTER `upload_documents`;
ALTER TABLE `rooms` ADD `occupancy_id` TINYINT(1) NOT NULL DEFAULT '0' AFTER `upload_documents`;
ALTER TABLE `rooms` ADD `bed_name` VARCHAR(50) NULL DEFAULT NULL AFTER `bed_id`, ADD `meal_name` VARCHAR(50) NULL DEFAULT NULL AFTER `bed_name`;
ALTER TABLE `hotels` ADD `enteredbyid` INT(10) NOT NULL DEFAULT '0' AFTER `organisation_id`;

ALTER TABLE `users` ADD `blood_group` VARCHAR(5) NULL DEFAULT NULL AFTER `opening_balance`, ADD `gender` TINYINT(1) NOT NULL DEFAULT '0' AFTER `blood_group`, ADD `d.o.b` VARCHAR(15) NULL DEFAULT NULL AFTER `gender`, ADD `citizenship` VARCHAR(20) NULL DEFAULT NULL AFTER `d.o.b`;
ALTER TABLE `users` CHANGE `d.o.b` `d_o_b` VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

ALTER TABLE `sequence_formats` ADD `padded_digits` INT(10) NOT NULL DEFAULT '0' AFTER `delimiter`;

-- ----------------------------------------------------
-- 28-01-2025
ALTER TABLE `credit_note_items`  ADD `item_name` VARCHAR(100) NULL DEFAULT NULL  AFTER `rolledback`,  ADD `is_service` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `item_name`,  ADD `service_data` TEXT NULL DEFAULT NULL  AFTER `is_service`,  ADD `type` VARCHAR(20) NULL DEFAULT NULL  AFTER `service_data`;
ALTER TABLE `debit_note_items`  ADD `item_name` VARCHAR(100) NULL DEFAULT NULL  AFTER `rolledback`,  ADD `is_service` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `item_name`,  ADD `service_data` TEXT NULL DEFAULT NULL  AFTER `is_service`,  ADD `type` VARCHAR(20) NULL DEFAULT NULL  AFTER `service_data`;

ALTER TABLE `payments` ADD `is_accounting` TINYINT(1) NOT NULL DEFAULT '0' AFTER `terms_and_condition`;

CREATE TABLE `tax_transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,  -- Unique identifier for the tax transaction
    `transaction_id` INT NOT NULL,        -- ID of the transaction (Foreign Key to transactions table)
    `customer_id` INT NOT NULL,           -- ID of the customer (Foreign Key to customers table)
    `tax_type` ENUM('VAT', 'GST', 'Other') NOT NULL,  -- Type of tax (e.g., VAT, GST, or other tax types)
    `net_amount` DECIMAL(15, 2) NOT NULL,  -- Net amount for the transaction before tax
    `tax_rate` DECIMAL(5, 2) NOT NULL,    -- Tax rate applied to the transaction (e.g., 5%, 18%, etc.)
    `tax_amount` DECIMAL(15, 2) NOT NULL, -- Tax amount calculated based on net amount and rate
    `gross_amount` DECIMAL(15, 2) NOT NULL, -- Total amount (Net + Tax)
    `hsn_code` VARCHAR(10) NOT NULL,      -- HSN/SAC code for the product/service (can be NULL if not applicable)
    `rolledback` TINYINT(1) DEFAULT 0,    -- Column to track if the transaction has been rolled back (0 = not rolled back, 1 = rolled back)
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Timestamp when the record is created
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

ALTER TABLE `tax_transactions` ADD `vendor_id` INT(10) NOT NULL DEFAULT '0' AFTER `customer_id`;
ALTER TABLE `tax_transactions` ADD `transaction_type` SMALLINT(3) NOT NULL DEFAULT '0' AFTER `transaction_id`;
ALTER TABLE `purchase_orders` CHANGE `customer_note` `customer_note` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
ALTER TABLE `bills` CHANGE `customer_note` `customer_note` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL

CREATE TABLE dsr_supplier_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dsr_id INT(10) NOT NULL DEFAULT 0,
    service_name VARCHAR(255) NULL,
    supplier_id INT(10) NOT NULL DEFAULT 0,
    supplier_name VARCHAR(50) NULL,
    gross_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    charges TEXT NULL,
    total DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    rolledback TINYINT(1) NOT NULL DEFAULT 0,
    is_accounting TINYINT(1) NOT NULL DEFAULT 0
);

ALTER TABLE dsr_supplier_summaries 
ADD COLUMN supplier_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0;

CREATE TABLE mice_supplier_summaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mice_id INT(10) NOT NULL DEFAULT 0,
    service_name VARCHAR(255) NULL,
    supplier_id INT(10) NOT NULL DEFAULT 0,
    supplier_name VARCHAR(50) NULL,
    gross_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    charges TEXT NULL,
    total DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    rolledback TINYINT(1) NOT NULL DEFAULT 0,
    is_accounting TINYINT(1) NOT NULL DEFAULT 0
);

ALTER TABLE mice_supplier_summaries 
ADD COLUMN supplier_amount DECIMAL(10,2) NOT NULL DEFAULT 0.0;

ALTER TABLE `dsr_supplier_summaries` ADD `enteredbyid` INT(10) NOT NULL DEFAULT '0' AFTER `is_accounting`;

ALTER TABLE `mice_supplier_summaries` ADD `enteredbyid` INT(10) NOT NULL DEFAULT '0' AFTER `is_accounting`;

ALTER TABLE `dsr_supplier_summaries`  ADD `service_data` TEXT NULL DEFAULT NULL  AFTER `is_accounting`;

ALTER TABLE `mice_supplier_summaries`  ADD `service_data` TEXT NULL DEFAULT NULL  AFTER `is_accounting`;

ALTER TABLE `mice_supplier_summaries` ADD `transaction_id` INT(10) NOT NULL DEFAULT '0' AFTER `mice_id`;

ALTER TABLE `dsr_supplier_summaries` ADD `transaction_id` INT(10) NOT NULL DEFAULT '0' AFTER `dsr_id`;

ALTER TABLE `quotation_items` ADD `warehouse` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `service_data`, ADD `zone` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `warehouse`, ADD `rack` MEDIUMINT(5) NOT NULL DEFAULT '0' AFTER `zone`, ADD `bin` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `rack`;

ALTER TABLE `sale_order_items` ADD `warehouse` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `service_data`, ADD `zone` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `warehouse`, ADD `rack` MEDIUMINT(5) NOT NULL DEFAULT '0' AFTER `zone`, ADD `bin` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `rack`

ALTER TABLE `invoice_items` ADD `warehouse` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `service_data`, ADD `zone` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `warehouse`, ADD `rack` MEDIUMINT(5) NOT NULL DEFAULT '0' AFTER `zone`, ADD `bin` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `rack`

ALTER TABLE `sale_order_items`  ADD `discount_before_tax` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `tax_amount`,  ADD `discount_after_tax` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `discount_before_tax`;

ALTER TABLE `invoice_items`  ADD `discount_before_tax` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `tax_amount`,  ADD `discount_after_tax` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `discount_before_tax`

ALTER TABLE `sale_orders`  ADD `currency_symbol` VARCHAR(50) NULL DEFAULT NULL  AFTER `currency_value`;
ALTER TABLE `quotations`  ADD `currency_symbol` VARCHAR(50) NULL DEFAULT NULL  AFTER `currency_value`;
ALTER TABLE `invoices`  ADD `currency_symbol` VARCHAR(50) NULL DEFAULT NULL  AFTER `currency_value`;
ALTER TABLE `purchase_orders`  ADD `currency_symbol` VARCHAR(50) NULL DEFAULT NULL  AFTER `currency_value`;
ALTER TABLE `bills`  ADD `currency_symbol` VARCHAR(50) NULL DEFAULT NULL  AFTER `currency_value`;

-- 19-03-2025
ALTER TABLE `bill_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts' AFTER `item_name`;
ALTER TABLE `invoice_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts' AFTER `item_name`;
ALTER TABLE `sale_order_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts' AFTER `item_name`;
ALTER TABLE `quotation_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts' AFTER `item_name`;
ALTER TABLE `g_r_n_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts';
ALTER TABLE `purchase_order_items` ADD `account_id` INT(10) NULL DEFAULT NULL COMMENT 'for transaction in accounts' AFTER `item_name`;
ALTER TABLE `g_r_n_s` CHANGE `grn_type` `purchase_from` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `bills`  ADD `purchase_from` VARCHAR(50) NULL DEFAULT NULL  AFTER `id`;
ALTER TABLE `purchase_orders` ADD `purchase_from` VARCHAR(50) NULL DEFAULT NULL AFTER `id`

-- 23-03-2025
ALTER TABLE `bill_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `invoice_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `purchase_order_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `g_r_n_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `sale_order_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;
ALTER TABLE `quotation_items` ADD `discount_percent` DECIMAL(10,2) NULL DEFAULT NULL AFTER `discount`;


-- 29-03-2025
ALTER TABLE `users` ADD `account_id` INT(10) NULL DEFAULT NULL AFTER `last_name`;
-- 25-03-2025
ALTER TABLE `credit_note_items` ADD `reason_type` VARCHAR(50) NULL DEFAULT NULL AFTER `type`, ADD `reason_diff` TINYINT(2) NOT NULL DEFAULT '1' COMMENT '1: Stock 2. Rate Difference' AFTER `reason_type`;
ALTER TABLE `debit_note_items` ADD `reason_type` VARCHAR(50) NULL DEFAULT NULL AFTER `type`, ADD `reason_diff` TINYINT(2) NOT NULL DEFAULT '1' COMMENT '1: Stock 2. Rate Difference' AFTER `reason_type`;
ALTER TABLE `accounts` ADD `account_posting` VARCHAR(50) NULL DEFAULT NULL AFTER `account_type`;
ALTER TABLE `accounts` ADD `as_on_date` VARCHAR(20) NULL DEFAULT NULL AFTER `is_disabled`, ADD `bank_branch` VARCHAR(200) NULL DEFAULT NULL AFTER `as_on_date`, ADD `bank_account_type` VARCHAR(20) NULL DEFAULT NULL AFTER `bank_branch`;
ALTER TABLE `employees` CHANGE `employee_status` `employee_status` SMALLINT(2) NULL DEFAULT '1';

-- 29-03-2025
ALTER TABLE `users` ADD `account_id` INT(10) NULL DEFAULT NULL AFTER `last_name`;
ALTER TABLE `users` ADD `category_type` INT(5) NULL DEFAULT NULL;
ALTER TABLE `accounts` ADD `account_posting_id` SMALLINT(3) NOT NULL DEFAULT '0' AFTER `account_posting`;