USE fortunes_quotation;

CREATE TABLE IF NOT EXISTS quotation_import_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT UNSIGNED NOT NULL,
  item_head VARCHAR(180) NOT NULL,
  specification TEXT NULL,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  qty DECIMAL(12,2) NOT NULL DEFAULT 0,
  unit VARCHAR(40) NOT NULL DEFAULT 'pcs',
  fob_rate_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
  fob_total_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_import_items_quotation FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$
CREATE PROCEDURE add_quotation_import_column_if_missing(IN p_column_name VARCHAR(64), IN p_column_definition TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'quotations'
      AND COLUMN_NAME = p_column_name
  ) THEN
    SET @sql = CONCAT('ALTER TABLE quotations ADD COLUMN ', p_column_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL add_quotation_import_column_if_missing('has_imported_materials', 'has_imported_materials TINYINT(1) NOT NULL DEFAULT 0');
CALL add_quotation_import_column_if_missing('import_subtotal_usd', 'import_subtotal_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('lc_fee_percent', 'lc_fee_percent DECIMAL(5,2) NULL DEFAULT 7');
CALL add_quotation_import_column_if_missing('lc_fee_usd', 'lc_fee_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('freight_usd', 'freight_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('insurance_usd', 'insurance_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('other_import_cost_usd', 'other_import_cost_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('total_import_cost_usd', 'total_import_cost_usd DECIMAL(12,2) NULL');
CALL add_quotation_import_column_if_missing('dollar_rate', 'dollar_rate DECIMAL(10,2) NULL DEFAULT 125');
CALL add_quotation_import_column_if_missing('total_import_cost_bdt', 'total_import_cost_bdt DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('local_subtotal_excluding_tax', 'local_subtotal_excluding_tax DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('ait_percent', 'ait_percent DECIMAL(5,2) NULL DEFAULT 5');
CALL add_quotation_import_column_if_missing('ait_amount', 'ait_amount DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('local_total_including_tax', 'local_total_including_tax DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('total_excluding_vat_ait', 'total_excluding_vat_ait DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('total_including_vat_ait', 'total_including_vat_ait DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('margin_percent', 'margin_percent DECIMAL(5,2) NULL');
CALL add_quotation_import_column_if_missing('margin_amount', 'margin_amount DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('final_quotation_amount', 'final_quotation_amount DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('dc_capacity_kwp', 'dc_capacity_kwp DECIMAL(10,2) NULL');
CALL add_quotation_import_column_if_missing('ac_capacity_kw', 'ac_capacity_kw DECIMAL(10,2) NULL');
CALL add_quotation_import_column_if_missing('cost_per_watt_excluding_tax', 'cost_per_watt_excluding_tax DECIMAL(10,2) NULL');
CALL add_quotation_import_column_if_missing('cost_per_watt_including_tax', 'cost_per_watt_including_tax DECIMAL(10,2) NULL');
CALL add_quotation_import_column_if_missing('cost_per_kw_excluding_tax', 'cost_per_kw_excluding_tax DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('cost_per_kw_including_tax', 'cost_per_kw_including_tax DECIMAL(14,2) NULL');
CALL add_quotation_import_column_if_missing('show_cost_per_watt_on_pdf', 'show_cost_per_watt_on_pdf TINYINT(1) NOT NULL DEFAULT 0');
CALL add_quotation_import_column_if_missing('import_tax_note', 'import_tax_note VARCHAR(255) NULL DEFAULT ''No VAT & AIT for Import Part''');

DROP PROCEDURE add_quotation_import_column_if_missing;
