-- SQL Migration for Company Bank Accounts
-- File: add_bank_accounts.sql
-- Description: Adds company_bank_accounts table and updates payments table

-- Create company_bank_accounts table
DROP TABLE IF EXISTS `company_bank_accounts`;
CREATE TABLE IF NOT EXISTS `company_bank_accounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `bank_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `bank_branch` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `account_holder_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ifsc_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_type` enum('current','savings','cash_credit','overdraft') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'current',
  `opening_balance` decimal(15,2) DEFAULT '0.00',
  `current_balance` decimal(15,2) DEFAULT '0.00',
  `is_default` tinyint(1) DEFAULT '0',
  `status` enum('active','inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `is_default` (`is_default`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Add bank_account_id column to payments table
ALTER TABLE `payments` 
ADD COLUMN `bank_account_id` int DEFAULT NULL AFTER `payment_mode`,
ADD KEY `bank_account_id` (`bank_account_id`);

-- Update payment_mode ENUM to include 'cheque'
ALTER TABLE `payments` 
MODIFY COLUMN `payment_mode` enum('cash','card','upi','bank_transfer','cheque') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'cash';

-- Add description for bank_account_id
-- This column links the payment to a specific bank account when payment mode is not cash