-- Migration: Monthly recurring expense templates + posted periods log
-- Idempotent where possible. Add expenses.recurring_template_id before recurring_expense_posts (FK to expenses).

CREATE TABLE IF NOT EXISTS `recurring_expenses` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) UNSIGNED NOT NULL,
  `category` varchar(50) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_recurring_branch_active` (`branch_id`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @dbname = DATABASE();
SET @exists = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = 'expenses' AND COLUMN_NAME = 'recurring_template_id'
);
SET @sql = IF(@exists = 0,
  'ALTER TABLE `expenses` ADD COLUMN `recurring_template_id` bigint(20) UNSIGNED NULL DEFAULT NULL AFTER `created_by`, ADD KEY `idx_exp_recurring_tpl` (`recurring_template_id`)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exists2 = (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = @dbname AND TABLE_NAME = 'expenses' AND CONSTRAINT_NAME = 'fk_expenses_recurring_template'
);
SET @sql2 = IF(@exists2 = 0,
  'ALTER TABLE `expenses` ADD CONSTRAINT `fk_expenses_recurring_template` FOREIGN KEY (`recurring_template_id`) REFERENCES `recurring_expenses` (`id`) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

CREATE TABLE IF NOT EXISTS `recurring_expense_posts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `recurring_expense_id` bigint(20) UNSIGNED NOT NULL,
  `period_yyyymm` char(6) NOT NULL COMMENT 'YYYYMM e.g. 202603',
  `expense_id` int(11) NOT NULL,
  `posted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_template_period` (`recurring_expense_id`,`period_yyyymm`),
  KEY `idx_rep_expense` (`expense_id`),
  CONSTRAINT `fk_rep_template` FOREIGN KEY (`recurring_expense_id`) REFERENCES `recurring_expenses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rep_expense` FOREIGN KEY (`expense_id`) REFERENCES `expenses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;