-- BEGIN deploy\iposcomp_ecomdb-store-settings-behavior-repair.sql
SET @db_name := DATABASE();

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'store_settings' AND COLUMN_NAME = 'checkout_hold_enabled') = 0,
  'ALTER TABLE `store_settings` ADD COLUMN `checkout_hold_enabled` TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT ''store_settings.checkout_hold_enabled already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'store_settings' AND COLUMN_NAME = 'checkout_hold_message') = 0,
  'ALTER TABLE `store_settings` ADD COLUMN `checkout_hold_message` VARCHAR(500) NULL',
  'SELECT ''store_settings.checkout_hold_message already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'checkout_hold_enabled') = 0,
  'ALTER TABLE `customers` ADD COLUMN `checkout_hold_enabled` TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT ''customers.checkout_hold_enabled already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'checkout_hold_reason') = 0,
  'ALTER TABLE `customers` ADD COLUMN `checkout_hold_reason` VARCHAR(500) NULL',
  'SELECT ''customers.checkout_hold_reason already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'customers' AND COLUMN_NAME = 'checkout_hold_updated_at') = 0,
  'ALTER TABLE `customers` ADD COLUMN `checkout_hold_updated_at` DATETIME NULL',
  'SELECT ''customers.checkout_hold_updated_at already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'store_settings' AND COLUMN_NAME = 'lock_prices_when_customer_pending_approval') = 0,
  'ALTER TABLE `store_settings` ADD COLUMN `lock_prices_when_customer_pending_approval` TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT ''store_settings.lock_prices_when_customer_pending_approval already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'store_settings' AND COLUMN_NAME = 'require_customer_approval') = 0,
  'ALTER TABLE `store_settings` ADD COLUMN `require_customer_approval` TINYINT(1) NOT NULL DEFAULT 0',
  'SELECT ''store_settings.require_customer_approval already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'store_settings_behavior_repair_done' AS status;
-- END deploy\iposcomp_ecomdb-store-settings-behavior-repair.sql

-- BEGIN deploy\iposcomp_ecomdb-orders-invoice-schema-repair.sql
SET @db_name := DATABASE();

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'invoices' AND COLUMN_NAME = 'invoice_type') = 0,
  'ALTER TABLE `invoices` ADD COLUMN `invoice_type` VARCHAR(30) NOT NULL DEFAULT ''sale''',
  'SELECT ''invoice_type already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'invoices' AND COLUMN_NAME = 'source_return_id') = 0,
  'ALTER TABLE `invoices` ADD COLUMN `source_return_id` BIGINT NULL',
  'SELECT ''source_return_id already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'invoices' AND COLUMN_NAME = 'external_invoice_no') = 0,
  'ALTER TABLE `invoices` ADD COLUMN `external_invoice_no` VARCHAR(120) NULL',
  'SELECT ''external_invoice_no already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'external_reference_no') = 0,
  'ALTER TABLE `orders` ADD COLUMN `external_reference_no` VARCHAR(120) NULL',
  'SELECT ''external_reference_no already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'external_reference_source') = 0,
  'ALTER TABLE `orders` ADD COLUMN `external_reference_source` VARCHAR(80) NULL',
  'SELECT ''external_reference_source already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'returned_qty') = 0,
  'ALTER TABLE `order_items` ADD COLUMN `returned_qty` DECIMAL(18,3) NOT NULL DEFAULT 0',
  'SELECT ''returned_qty already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'return_status') = 0,
  'ALTER TABLE `order_items` ADD COLUMN `return_status` VARCHAR(30) NOT NULL DEFAULT ''none''',
  'SELECT ''return_status already exists'' AS status'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'orders_invoice_schema_repair_done' AS status;
-- END deploy\iposcomp_ecomdb-orders-invoice-schema-repair.sql

-- BEGIN deploy\iposcomp_ecomdb-management-dashboard-repair.sql
INSERT INTO system_settings (setting_key, setting_value)
VALUES ('management_dashboard_enabled', '1')
ON DUPLICATE KEY UPDATE setting_value = '1';

INSERT INTO permissions
  (module_code, action_code, permission_code, permission_name, description, is_active, sort_order)
VALUES
  ('management_dashboard', 'command_view', 'management_dashboard.command_view', 'Command View Dashboard', 'Open the command-level operational dashboard.', 1, 2100),
  ('management_dashboard', 'payment_verification', 'management_dashboard.payment_verification', 'Payment Verification Dashboard', 'Verify and manage payment-focused order queues.', 1, 2101),
  ('management_dashboard', 'payment_desk', 'management_dashboard.payment_desk', 'Payment Verification Desk Alias', 'Legacy alias for payment verification dashboard access.', 1, 2101),
  ('management_dashboard', 'packing_desk', 'management_dashboard.packing_desk', 'Packing Desk Dashboard', 'Pick, pack, and prepare order queues.', 1, 2102),
  ('management_dashboard', 'shipping_desk', 'management_dashboard.shipping_desk', 'Shipping Desk Dashboard', 'Manage shipment and dispatch queues.', 1, 2103),
  ('management_dashboard', 'returns_desk', 'management_dashboard.returns_desk', 'Returns Desk Dashboard', 'Manage return authorization, receiving, and completion queues.', 1, 2104),
  ('management_dashboard', 'toggle', 'management_dashboard.toggle', 'Toggle Management Dashboard', 'Turn the Management Dashboard feature on or off.', 1, 2105)
ON DUPLICATE KEY UPDATE
  module_code = VALUES(module_code),
  action_code = VALUES(action_code),
  permission_name = VALUES(permission_name),
  description = VALUES(description),
  is_active = 1,
  sort_order = VALUES(sort_order);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.permission_code IN (
  'management_dashboard.command_view',
  'management_dashboard.payment_verification',
  'management_dashboard.packing_desk',
  'management_dashboard.shipping_desk',
  'management_dashboard.returns_desk'
)
WHERE r.role_code = 'super_admin';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT rp.role_id, pv.id
FROM role_permissions rp
JOIN permissions legacy ON legacy.id = rp.permission_id
JOIN permissions pv ON pv.permission_code = 'management_dashboard.payment_verification'
WHERE legacy.permission_code = 'management_dashboard.payment_desk';

INSERT INTO api_permission_map
  (api_prefix, path_pattern, module_code, permission_code, match_type, is_public, is_active, description)
VALUES
  ('/api/dashboard/management', '/api/dashboard/management', 'management_dashboard', NULL, 'prefix', 0, 1, 'Management dashboard APIs.')
ON DUPLICATE KEY UPDATE
  path_pattern = VALUES(path_pattern),
  module_code = VALUES(module_code),
  permission_code = VALUES(permission_code),
  match_type = VALUES(match_type),
  is_public = 0,
  is_active = 1,
  description = VALUES(description);
-- END deploy\iposcomp_ecomdb-management-dashboard-repair.sql

-- BEGIN deploy\iposcomp_ecomdb-offline-license-tables-repair.sql
CREATE TABLE IF NOT EXISTS LocalOfflineLicenseInstallation (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  InstallId VARCHAR(80) NOT NULL UNIQUE,
  InstanceSecretHash VARCHAR(128) NOT NULL,
  RegisteredDomain VARCHAR(255) NULL,
  BasePath VARCHAR(255) NOT NULL DEFAULT '/',
  FingerprintHash VARCHAR(128) NULL,
  CreatedOn DATETIME NOT NULL,
  UpdatedOn DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS LocalOfflineLicenseState (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  InstallId VARCHAR(80) NOT NULL UNIQUE,
  LicenseId VARCHAR(120) NULL,
  SignedLicenseKey MEDIUMTEXT NOT NULL,
  LicenseHash VARCHAR(128) NOT NULL,
  Status VARCHAR(40) NOT NULL,
  AccessMode VARCHAR(80) NOT NULL,
  LicenseIssuedAt DATETIME NULL,
  LicenseExpiresAt DATETIME NULL,
  ValidDays INT NULL,
  MaxUses INT NULL,
  UseCount INT NOT NULL DEFAULT 0,
  LastCheckOn DATETIME NULL,
  CreatedOn DATETIME NOT NULL,
  UpdatedOn DATETIME NOT NULL,
  INDEX idx_local_license_hash (LicenseHash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS LocalOfflineLicenseEvents (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  EventType VARCHAR(80) NOT NULL,
  Severity VARCHAR(20) NOT NULL,
  Message VARCHAR(500) NOT NULL,
  DataJson JSON NULL,
  CreatedOn DATETIME NOT NULL,
  INDEX idx_local_license_events_created (CreatedOn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS LocalOfflineLicenseChallenges (
  AutoNo BIGINT AUTO_INCREMENT PRIMARY KEY,
  ChallengeId VARCHAR(80) NOT NULL UNIQUE,
  ChallengeCode MEDIUMTEXT NOT NULL,
  Nonce VARCHAR(80) NOT NULL,
  UsedOn DATETIME NULL,
  CreatedOn DATETIME NOT NULL,
  ExpiresOn DATETIME NOT NULL,
  INDEX idx_local_license_challenges_created (CreatedOn)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- END deploy\iposcomp_ecomdb-offline-license-tables-repair.sql