-- 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 updated in vokks,
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 updated in vokks,
ALTER TABLE `users` ADD `account_id` INT(10) NULL DEFAULT NULL AFTER `last_name`;

-- 25-03-2025 updated in vokks,
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 updated in vokks,
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`;



-- Megamarket 28-08-2025 updated in vokks,
ALTER TABLE `invoices` ADD `reverse_charge` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '0 for no reverse charge' AFTER `payment_term_day`;
ALTER TABLE `invoices` ADD `is_gst_input` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'GST Input (Input Tax Credit, ITC) means the GST you paid on purchases that you can claim back from GST liability on sales' AFTER `amount_paid`;
ALTER TABLE `bills` ADD `is_gst_input` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'GST Input (Input Tax Credit, ITC) means the GST you paid on purchases that you can claim back from GST liability on sales' AFTER `amount_paid`;
ALTER TABLE `bills` ADD `input_tax` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'GST Input (Input Tax Credit, ITC) means the GST you paid on purchases that you can claim back from GST liability on sales' AFTER `amount_paid`;

--Megamarket 04-29-2025 updated in vokks ,
ALTER TABLE `bills` ADD `input_tax` TINYINT(1) NOT NULL DEFAULT '0' COMMENT '1 Eligible and 2 non Eligible' AFTER `transaction_type`, ADD `is_rcm` SMALLINT(2) NOT NULL DEFAULT '0' AFTER `input_tax`;
ALTER TABLE `invoices` ADD `state_id` INT(10) NOT NULL DEFAULT '0' AFTER `address`;
ALTER TABLE `sale_orders`  ADD `state_id` INT(10) NOT NULL DEFAULT '0'  AFTER `address`;
ALTER TABLE `quotations`  ADD `state_id` INT(10) NOT NULL DEFAULT '0'  AFTER `address`;
ALTER TABLE `purchase_orders`  ADD `state_id` INT(10) NOT NULL DEFAULT '0'  AFTER `address`;
ALTER TABLE `g_r_n_s` ADD `state_id` INT(10) NOT NULL DEFAULT '0';
ALTER TABLE `bills` ADD `state_id` INT(10) NOT NULL DEFAULT '0' AFTER `address`;

-- Billing GST 15-09-2025 updated in vokks,
ALTER TABLE `invoice_items` ADD `hsn_code` VARCHAR(10) NULL DEFAULT NULL AFTER `item_name`;
ALTER TABLE `quotation_items` ADD `hsn_code` VARCHAR(10) NULL DEFAULT NULL AFTER `item_name`;
ALTER TABLE `sale_order_items` ADD `hsn_code` VARCHAR(10) NULL DEFAULT NULL AFTER `item_name`;

-- Naksha 19-09-2025 updated in Vokks , 
ALTER TABLE `credit_notes`  ADD `amount_paid` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `is_accounting`;
ALTER TABLE `debit_notes`  ADD `amount_paid` DECIMAL(10,2) NOT NULL DEFAULT '0.0'  AFTER `is_accounting`;
ALTER TABLE `invoice_items` ADD `credit_note_qty` DECIMAL(10,2) NULL DEFAULT NULL AFTER `quantity`;
ALTER TABLE `bill_items` ADD `debit_note_qty` DECIMAL(10,2) NULL DEFAULT NULL AFTER `quantity`
ALTER TABLE `invoices` ADD `is_credit_note_close` TINYINT(1) NOT NULL DEFAULT '0' AFTER `place_of_supply_id`;
ALTER TABLE `accounts` ADD `account_group_id` INT(10) NOT NULL DEFAULT '0' AFTER `parent_id`;
ALTER TABLE `expenses` ADD `tax_amount` DECIMAL(10,2) NOT NULL DEFAULT '0.0' AFTER `description`, ADD `tax_percent` INT(10) NULL DEFAULT '0' AFTER `tax_amount`;
ALTER TABLE `bills` ADD `is_debit_note_close` TINYINT(1) NOT NULL DEFAULT '0';

-- Vokks 14-10-2025
ALTER TABLE `invoices` ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL AFTER `currency_symbol`;
ALTER TABLE `bills` ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL AFTER `currency_symbol`;
ALTER TABLE `quotations`  ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL  AFTER `currency_symbol`;
ALTER TABLE `sale_orders`  ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL  AFTER `currency_symbol`;
ALTER TABLE `purchase_orders`  ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL  AFTER `currency_symbol`;
ALTER TABLE `g_r_n_s`  ADD `exchange_rate` DECIMAL(10,2) NULL DEFAULT NULL  AFTER `currency_symbol`;

-- Naksha 30-10-2025
ALTER TABLE `dsr_service_flights` ADD `multi_city` TEXT NULL DEFAULT NULL AFTER `gross_amount`;
ALTER TABLE `dsr_service_flights` ADD `return_date` VARCHAR(20) NULL DEFAULT NULL AFTER `travel_date`;
ALTER TABLE `dsr_supplier_summaries` ADD `is_billed` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_accounting`;
ALTER TABLE `mice_supplier_summaries` ADD `is_billed` TINYINT(1) NOT NULL DEFAULT '0' AFTER `enteredbyid`;

-- Naksha 10-11-2025
ALTER TABLE `dsr_supplier_summaries` ADD `currency` VARCHAR(20) NULL DEFAULT NULL AFTER `total`, ADD `currency_symbol` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `currency`;
ALTER TABLE `dsr_supplier_summaries` ADD `exchange_rate` DOUBLE(10,2) NOT NULL DEFAULT '0.0' AFTER `currency_symbol`;
ALTER TABLE `bills`  ADD `is_dsr` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `supplier_summary_ids`,  ADD `is_mice` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `is_dsr`;
ALTER TABLE `bills`  ADD `is_service` TINYINT(1) NOT NULL DEFAULT '0'  AFTER `supplier_summary_ids`;
ALTER TABLE `g_r_n_s` ADD `address` TEXT NULL DEFAULT NULL AFTER `email`;
ALTER TABLE `g_r_n_s` ADD `delivery_address` TEXT NULL DEFAULT NULL AFTER `email`;
ALTER TABLE `flights` CHANGE `air_line_code` `air_line_code` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `mice_service_flights` CHANGE `air_line_code` `air_line_code` VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
ALTER TABLE `dsr_service_tours` ADD `package_name` VARCHAR(100) NULL DEFAULT NULL AFTER `tour_package_id`;
ALTER TABLE `mice_service_tours` ADD `package_name` VARCHAR(100) NULL DEFAULT NULL AFTER `tour_package_id`;
ALTER TABLE `dsr_service_hotels` ADD `room_number` VARCHAR(100) NULL DEFAULT NULL AFTER `room_id`;
ALTER TABLE `mice_service_hotels` ADD `room_number` VARCHAR(100) NULL DEFAULT NULL AFTER `room_id`;
ALTER TABLE `mice_service_tours` ADD `destination` VARCHAR(100) NULL DEFAULT NULL AFTER `is_transport`;
ALTER TABLE `dsr_service_tours` ADD `destination` VARCHAR(100) NULL DEFAULT NULL AFTER `is_transport`;
ALTER TABLE `mices` ADD `address` TEXT NULL DEFAULT NULL;
ALTER TABLE `dsrs` ADD `address` TEXT NULL DEFAULT NULL;
ALTER TABLE `dsrs` CHANGE `address` `address` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `mices` CHANGE `address` `address` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;