-- Security hardening: login throttling and supporting indexes

CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `ip_address` VARCHAR(64) NOT NULL,
  `attempts` INT NOT NULL DEFAULT 1,
  `last_attempt_at` DATETIME NOT NULL,
  `locked_until` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_login_attempts_user_ip` (`username`,`ip_address`),
  KEY `idx_login_attempts_locked` (`locked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET @sql := IF (
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'sales_invoices'
      AND index_name = 'idx_si_status_date_party'
  ),
  'SELECT 1',
  'ALTER TABLE `sales_invoices` ADD INDEX `idx_si_status_date_party` (`status`, `invoice_date`, `party_id`)' 
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'sales_invoices'
      AND index_name = 'idx_si_date_id'
  ),
  'SELECT 1',
  'ALTER TABLE `sales_invoices` ADD INDEX `idx_si_date_id` (`invoice_date`, `id`)' 
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'sales_items'
      AND index_name = 'idx_sales_items_invoice_item'
  ),
  'SELECT 1',
  'ALTER TABLE `sales_items` ADD INDEX `idx_sales_items_invoice_item` (`invoice_id`, `item_id`)' 
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'cashbox_transactions'
      AND index_name = 'idx_cbt_cashbox_date_id'
  ),
  'SELECT 1',
  'ALTER TABLE `cashbox_transactions` ADD INDEX `idx_cbt_cashbox_date_id` (`cashbox_id`, `trx_date`, `id`)' 
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF (
  EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'cashbox_transactions'
      AND index_name = 'idx_cbt_ref'
  ),
  'SELECT 1',
  'ALTER TABLE `cashbox_transactions` ADD INDEX `idx_cbt_ref` (`ref_type`, `ref_id`)' 
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
