-- Phase 10: Banks module - phase 1
-- Creates bank accounts + bank transactions tables
-- Seeds bank permissions and assigns them to Admin role

CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `bank_name` VARCHAR(150) NULL,
  `account_no` VARCHAR(100) NULL,
  `iban` VARCHAR(100) NULL,
  `opening_balance` DECIMAL(18,2) NOT NULL DEFAULT 0,
  `currency_code` VARCHAR(10) NOT NULL DEFAULT 'EGP',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bank_transactions` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `bank_account_id` INT NOT NULL,
  `trx_date` DATETIME NOT NULL,
  `trx_type` ENUM('in','out') NOT NULL,
  `amount` DECIMAL(18,2) NOT NULL,
  `source_type` VARCHAR(50) NULL,
  `source_id` INT NULL,
  `ref_no` VARCHAR(100) NULL,
  `notes` TEXT NULL,
  `user_id` INT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_bank_transactions_bank_date` (`bank_account_id`,`trx_date`),
  CONSTRAINT `fk_bank_transactions_bank_account` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `permissions` (`code`,`name`,`module`) VALUES
('banks.accounts.view', 'عرض الحسابات البنكية', 'banks'),
('banks.accounts.create', 'إضافة/تعديل حساب بنكي', 'banks'),
('banks.txns.view', 'عرض حركات البنوك', 'banks'),
('banks.txns.create', 'إضافة حركة بنك', 'banks');

INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT 1, p.id
FROM `permissions` p
WHERE p.code IN ('banks.accounts.view','banks.accounts.create','banks.txns.view','banks.txns.create');