
ALTER TABLE `quotation_items`
ADD COLUMN `line_discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00 AFTER `tax_rate`;

ALTER TABLE `items`
ADD COLUMN `is_active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `is_third_schedule`,
ADD KEY `idx_items_is_active` (`is_active`);


ALTER TABLE `expenses`
  ADD COLUMN `expense_payment_group_id` VARCHAR(36) NULL DEFAULT NULL AFTER `shift_id`;

ALTER TABLE `expenses`
  ADD COLUMN `branch_id` INT NULL DEFAULT NULL AFTER `store_id`;





CREATE TABLE IF NOT EXISTS `item_line_discounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL COMMENT 'FK to items.id',
  `discount_type` enum('percent','fixed') NOT NULL COMMENT 'Matches sale_items line discount',
  `discount_value` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `starts_at` date DEFAULT NULL COMMENT 'NULL = no start date',
  `expires_at` date DEFAULT NULL COMMENT 'NULL = no expiry',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_item_line_discounts_item_id` (`item_id`),
  KEY `idx_item_line_discounts_active_dates` (`is_active`, `expires_at`, `starts_at`),
  CONSTRAINT `fk_item_line_discounts_item`
    FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;




-- 1) Create right if it does not already exist
INSERT INTO rights (name, description, category)
SELECT 'ITR Assistant', 'Access and use ITR Assistant chat/call features', 'Settings'
WHERE NOT EXISTS (
    SELECT 1 FROM rights WHERE LOWER(name) = LOWER('ITR Assistant')
);

-- 2) Assign this right to Admin role (role_id = 1) if not already assigned
INSERT INTO role_rights (role_id, right_id)
SELECT 1, r.id
FROM rights r
WHERE LOWER(r.name) = LOWER('ITR Assistant')
  AND NOT EXISTS (
      SELECT 1
      FROM role_rights rr
      WHERE rr.role_id = 1
        AND rr.right_id = r.id
  );






ALTER TABLE `fbr_config`
ADD COLUMN `display_name` ENUM('FBR','PRA') NOT NULL DEFAULT 'FBR' AFTER `is_enabled`;

UPDATE `fbr_config`
SET `display_name` = CASE
  WHEN UPPER(TRIM(COALESCE(`display_name`, ''))) = 'PRA' THEN 'PRA'
  ELSE 'FBR'
END;








CREATE TABLE IF NOT EXISTS `zatca_phase2_invoices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sale_id` INT NOT NULL,
  `invoice_number` VARCHAR(100) NOT NULL,
  `invoice_type` ENUM('standard','simplified','credit_note','debit_note') NOT NULL DEFAULT 'simplified',

  `uuid` CHAR(36) NOT NULL,
  `icv` BIGINT UNSIGNED DEFAULT NULL,
  `previous_invoice_hash` VARCHAR(512) DEFAULT NULL,
  `invoice_hash` VARCHAR(512) DEFAULT NULL,

  `unsigned_xml` LONGTEXT,
  `signed_xml` LONGTEXT,
  `canonical_xml_hash` VARCHAR(512) DEFAULT NULL,

  `qr_tlv_base64` LONGTEXT,
  `authority_submission_type` ENUM('clearance','reporting') DEFAULT NULL,
  `authority_invoice_ref` VARCHAR(255) DEFAULT NULL,
  `authority_status` VARCHAR(50) DEFAULT NULL,
  `authority_response_code` VARCHAR(50) DEFAULT NULL,
  `authority_response_message` TEXT,

  `status` ENUM(
    'draft',
    'signed',
    'queued',
    'submitted',
    'cleared',
    'reported',
    'rejected',
    'retry_pending',
    'failed'
  ) NOT NULL DEFAULT 'draft',

  `retry_count` INT NOT NULL DEFAULT 0,
  `next_retry_at` DATETIME DEFAULT NULL,
  `last_error_at` DATETIME DEFAULT NULL,
  `last_error_message` TEXT,

  `submitted_at` DATETIME DEFAULT NULL,
  `cleared_at` DATETIME DEFAULT NULL,
  `reported_at` DATETIME DEFAULT NULL,

  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_zatca_p2_uuid` (`uuid`),
  UNIQUE KEY `uq_zatca_p2_sale_id` (`sale_id`),
  KEY `idx_zatca_p2_sale_id` (`sale_id`),
  KEY `idx_zatca_p2_invoice_number` (`invoice_number`),
  KEY `idx_zatca_p2_status` (`status`),
  KEY `idx_zatca_p2_next_retry_at` (`next_retry_at`),
  KEY `idx_zatca_p2_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   2) Status history
   --------------------------------------------------------- */
CREATE TABLE IF NOT EXISTS `zatca_phase2_status_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `zatca_phase2_invoice_id` BIGINT UNSIGNED NOT NULL,
  `from_status` VARCHAR(50) DEFAULT NULL,
  `to_status` VARCHAR(50) NOT NULL,
  `event_source` ENUM('system','user','worker','api') NOT NULL DEFAULT 'system',
  `reason_code` VARCHAR(100) DEFAULT NULL,
  `reason_message` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_zatca_p2_hist_invoice` (`zatca_phase2_invoice_id`),
  KEY `idx_zatca_p2_hist_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   3) API logs
   --------------------------------------------------------- */
CREATE TABLE IF NOT EXISTS `zatca_phase2_api_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `zatca_phase2_invoice_id` BIGINT UNSIGNED DEFAULT NULL,
  `endpoint` VARCHAR(255) NOT NULL,
  `http_method` VARCHAR(10) NOT NULL DEFAULT 'POST',
  `request_headers` LONGTEXT,
  `request_body` LONGTEXT,
  `response_status` INT DEFAULT NULL,
  `response_headers` LONGTEXT,
  `response_body` LONGTEXT,
  `duration_ms` INT DEFAULT NULL,
  `correlation_id` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_zatca_p2_api_invoice` (`zatca_phase2_invoice_id`),
  KEY `idx_zatca_p2_api_status` (`response_status`),
  KEY `idx_zatca_p2_api_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   4) Worker jobs
   --------------------------------------------------------- */
CREATE TABLE IF NOT EXISTS `zatca_phase2_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `zatca_phase2_invoice_id` BIGINT UNSIGNED NOT NULL,
  `job_type` ENUM('submit_clearance','submit_reporting','retry_submission','sync_status') NOT NULL,
  `status` ENUM('pending','processing','done','failed','cancelled') NOT NULL DEFAULT 'pending',
  `attempt_count` INT NOT NULL DEFAULT 0,
  `max_attempts` INT NOT NULL DEFAULT 8,
  `run_after` DATETIME DEFAULT NULL,
  `locked_at` DATETIME DEFAULT NULL,
  `locked_by` VARCHAR(100) DEFAULT NULL,
  `last_error` TEXT,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_zatca_p2_jobs_invoice` (`zatca_phase2_invoice_id`),
  KEY `idx_zatca_p2_jobs_status_run` (`status`, `run_after`),
  KEY `idx_zatca_p2_jobs_locked` (`locked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   5) Device state (ICV/hash chain continuity)
   --------------------------------------------------------- */
CREATE TABLE IF NOT EXISTS `zatca_phase2_device_state` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store_id` INT NOT NULL,
  `terminal_id` INT DEFAULT NULL,
  `device_identifier` VARCHAR(150) NOT NULL,

  `last_icv` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `last_invoice_uuid` CHAR(36) DEFAULT NULL,
  `last_invoice_hash` VARCHAR(512) DEFAULT NULL,

  `last_submission_at` DATETIME DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_zatca_p2_device` (`store_id`, `terminal_id`, `device_identifier`),
  KEY `idx_zatca_p2_device_store` (`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   6) Certificate metadata
   --------------------------------------------------------- */
CREATE TABLE IF NOT EXISTS `zatca_phase2_certificates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `store_id` INT DEFAULT NULL,
  `environment` ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
  `device_identifier` VARCHAR(150) DEFAULT NULL,

  `certificate_type` ENUM('compliance_csid','production_csid') NOT NULL DEFAULT 'compliance_csid',
  `certificate_serial` VARCHAR(255) DEFAULT NULL,
  `issuer_dn` TEXT,
  `subject_dn` TEXT,
  `valid_from` DATETIME DEFAULT NULL,
  `valid_to` DATETIME DEFAULT NULL,

  `csr_pem` LONGTEXT,
  `certificate_pem` LONGTEXT,
  `private_key_ref` VARCHAR(255) DEFAULT NULL,
  `fingerprint_sha256` VARCHAR(128) DEFAULT NULL,

  `is_active` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_zatca_p2_cert_store` (`store_id`),
  KEY `idx_zatca_p2_cert_env` (`environment`),
  KEY `idx_zatca_p2_cert_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* ---------------------------------------------------------
   7) sales table bridge column + index (old-MySQL safe)
   --------------------------------------------------------- */
SET @col_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sales'
    AND COLUMN_NAME = 'zatca_phase2_invoice_id'
);

SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `sales` ADD COLUMN `zatca_phase2_invoice_id` BIGINT UNSIGNED NULL AFTER `is_kpra_enabled`',
  'SELECT "Column sales.zatca_phase2_invoice_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sales'
    AND INDEX_NAME = 'idx_sales_zatca_phase2_invoice_id'
);

SET @sql := IF(
  @idx_exists = 0,
  'ALTER TABLE `sales` ADD INDEX `idx_sales_zatca_phase2_invoice_id` (`zatca_phase2_invoice_id`)',
  'SELECT "Index idx_sales_zatca_phase2_invoice_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

/* ---------------------------------------------------------
   8) Ensure setting category exists
   --------------------------------------------------------- */
INSERT INTO `setting_categories` (`name`, `code`)
SELECT 'ZATCA Integration', 'zatca_integration'
WHERE NOT EXISTS (
  SELECT 1 FROM `setting_categories` WHERE `code` = 'zatca_integration'
);

SET @zatca_category_id := (
  SELECT `id` FROM `setting_categories` WHERE `code` = 'zatca_integration' LIMIT 1
);

/* ---------------------------------------------------------
   9) Insert settings (if not exists)
   --------------------------------------------------------- */
INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'ZATCA Mode', 'zatca_mode', 'select', 'phase1', 'phase1',
       'ZATCA integration mode: phase1 or phase2', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_mode');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'Enable ZATCA Phase 2', 'zatca_phase2_enabled', 'boolean', '0', '0',
       'Enable Phase 2 (clearance/reporting)', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_enabled');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'ZATCA Phase 2 Environment', 'zatca_phase2_environment', 'select', 'sandbox', 'sandbox',
       'sandbox or production environment', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_environment');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'Enable ZATCA Phase 2 Worker', 'zatca_phase2_worker_enabled', 'boolean', '1', '1',
       'Enable background worker for retries/reporting', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_worker_enabled');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'ZATCA Retry Delay Seconds', 'zatca_phase2_retry_delay_seconds', 'number', '120', '120',
       'Base retry delay in seconds', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_retry_delay_seconds');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'ZATCA Max Retries', 'zatca_phase2_max_retries', 'number', '8', '8',
       'Maximum retries for failed submissions', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_max_retries');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, @zatca_category_id, 'ZATCA Reporting SLA Hours', 'zatca_phase2_reporting_sla_hours', 'number', '24', '24',
       'Simplified invoice reporting deadline in hours', 1
WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `code` = 'zatca_phase2_reporting_sla_hours');

/* ---------------------------------------------------------
   10) Add setting options safely
   --------------------------------------------------------- */
INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT s.id, 'Phase 1', 'phase1'
FROM `settings` s
WHERE s.code = 'zatca_mode'
  AND NOT EXISTS (
    SELECT 1 FROM `setting_options` so
    WHERE so.setting_id = s.id AND so.option_value = 'phase1'
  );

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT s.id, 'Phase 2', 'phase2'
FROM `settings` s
WHERE s.code = 'zatca_mode'
  AND NOT EXISTS (
    SELECT 1 FROM `setting_options` so
    WHERE so.setting_id = s.id AND so.option_value = 'phase2'
  );

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT s.id, 'Sandbox', 'sandbox'
FROM `settings` s
WHERE s.code = 'zatca_phase2_environment'
  AND NOT EXISTS (
    SELECT 1 FROM `setting_options` so
    WHERE so.setting_id = s.id AND so.option_value = 'sandbox'
  );

INSERT INTO `setting_options` (`setting_id`, `option_label`, `option_value`)
SELECT s.id, 'Production', 'production'
FROM `settings` s
WHERE s.code = 'zatca_phase2_environment'
  AND NOT EXISTS (
    SELECT 1 FROM `setting_options` so
    WHERE so.setting_id = s.id AND so.option_value = 'production'
  );




INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, sc.id, 'ZATCA Phase 2 API Base URL', 'zatca_phase2_api_base_url', 'text',
       'https://sandbox.fatoora.zatca.gov.sa', 'https://sandbox.fatoora.zatca.gov.sa',
       'Base URL for ZATCA Phase 2 APIs', 1
FROM setting_categories sc
WHERE sc.code = 'zatca_integration'
  AND NOT EXISTS (SELECT 1 FROM settings WHERE code = 'zatca_phase2_api_base_url');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, sc.id, 'ZATCA Phase 2 API Token', 'zatca_phase2_api_token', 'text',
       '', '',
       'Authorization token/credential for ZATCA APIs', 1
FROM setting_categories sc
WHERE sc.code = 'zatca_integration'
  AND NOT EXISTS (SELECT 1 FROM settings WHERE code = 'zatca_phase2_api_token');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, sc.id, 'ZATCA Clearance Endpoint', 'zatca_phase2_clearance_endpoint', 'text',
       '/api/v1/invoices/clearance/single', '/api/v1/invoices/clearance/single',
       'Endpoint path for standard invoice clearance', 1
FROM setting_categories sc
WHERE sc.code = 'zatca_integration'
  AND NOT EXISTS (SELECT 1 FROM settings WHERE code = 'zatca_phase2_clearance_endpoint');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, sc.id, 'ZATCA Reporting Endpoint', 'zatca_phase2_reporting_endpoint', 'text',
       '/api/v1/invoices/reporting/single', '/api/v1/invoices/reporting/single',
       'Endpoint path for simplified invoice reporting', 1
FROM setting_categories sc
WHERE sc.code = 'zatca_integration'
  AND NOT EXISTS (SELECT 1 FROM settings WHERE code = 'zatca_phase2_reporting_endpoint');

INSERT INTO `settings`
(`store_id`,`category_id`,`name`,`code`,`type`,`value`,`default_value`,`description`,`is_active`)
SELECT NULL, sc.id, 'ZATCA Device Identifier', 'zatca_phase2_device_identifier', 'text',
       '', '',
       'Optional fixed EGS device identifier for hash chain', 1
FROM setting_categories sc
WHERE sc.code = 'zatca_integration'
  AND NOT EXISTS (SELECT 1 FROM settings WHERE code = 'zatca_phase2_device_identifier');




INSERT INTO `settings`
(`store_id`, `category_id`, `name`, `code`, `type`, `value`, `default_value`, `description`, `is_active`)
VALUES
(NULL, 1, 'Print Logo', 'print_logo', 'boolean', '1', '1', 'When enabled, the receipt prints logo; when disabled, logo is hidden.', 1)
ON DUPLICATE KEY UPDATE
`name` = VALUES(`name`),
`category_id` = VALUES(`category_id`),
`type` = VALUES(`type`),
`value` = VALUES(`value`),
`default_value` = VALUES(`default_value`),
`description` = VALUES(`description`),
`is_active` = VALUES(`is_active`);





SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'account_transactions'
      AND COLUMN_NAME = 'is_reconciled'
);

SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE account_transactions ADD COLUMN is_reconciled TINYINT(1) NOT NULL DEFAULT 0 AFTER card_transaction_ref',
    'SELECT "is_reconciled already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Optional right for menu/page control
INSERT INTO rights (name, description, category)
SELECT 'Card Reconciliation', 'View and reconcile card transactions', 'Accounts'
WHERE NOT EXISTS (
    SELECT 1 FROM rights WHERE LOWER(name) = LOWER('Card Reconciliation')
);







INSERT INTO chart_of_accounts
(
    account_code,
    account_name,
    account_type,
    account_category,
    parent_account_id,
    account_level,
    is_active,
    description,
    opening_balance,
    current_balance,
    store_id
)
SELECT
    '1104',
    'Card Unsettled Receipts',
    'asset',
    'current_asset',
    19,
    3,
    1,
    'Card payments pending settlement from bank/processor',
    0.00,
    0.00,
    1
WHERE NOT EXISTS (
    SELECT 1 FROM chart_of_accounts WHERE account_code = '1104'
);

-- 2) Create account mapping for unsettled card account (if missing)
INSERT INTO account_mappings
(
    mapping_code,
    mapping_name,
    account_id,
    description,
    is_active
)
SELECT
    'ast_CardUnsettled',
    'Card Unsettled Receipts',
    coa.id,
    'Card receipts awaiting settlement',
    1
FROM chart_of_accounts coa
WHERE coa.account_code = '1104'
  AND NOT EXISTS (
      SELECT 1 FROM account_mappings WHERE mapping_code = 'ast_CardUnsettled'
  );




SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'default_branch_id'
);

SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE users ADD COLUMN default_branch_id INT NOT NULL DEFAULT 1 AFTER assigned_store_id',
    'SELECT "default_branch_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND INDEX_NAME = 'idx_users_default_branch_id'
);

SET @sql := IF(
    @idx_exists = 0,
    'ALTER TABLE users ADD KEY idx_users_default_branch_id (default_branch_id)',
    'SELECT "idx_users_default_branch_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Backfill existing rows with branch 1 when missing/invalid
UPDATE users
SET default_branch_id = 1
WHERE default_branch_id IS NULL OR default_branch_id = 0;



SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'items'
      AND COLUMN_NAME = 'description'
);

SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE items ADD COLUMN description TEXT NULL AFTER name',
    'SELECT "description already exists on items"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

