-- ============================================================
-- Schema Update 4 — Banks + Payment Method
-- Run this ONCE
-- ============================================================

-- Register banks
CREATE TABLE IF NOT EXISTS `Banks` (
  `id`         INT NOT NULL AUTO_INCREMENT,
  `bank_name`  VARCHAR(100) NOT NULL,
  `account_no` VARCHAR(50) DEFAULT NULL,
  `branch`     VARCHAR(100) DEFAULT NULL,
  `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample banks (edit as needed)
INSERT IGNORE INTO `Banks` (`id`,`bank_name`,`account_no`) VALUES
(1,'Bank of Ceylon (BOC)',''),
(2,"People's Bank",''),
(3,'NSB',''),
(4,'Commercial Bank',''),
(5,'Sampath Bank','');

-- Add payment method to Payments
ALTER TABLE `Payments`
  ADD COLUMN `payment_method` ENUM('cash','bank') NOT NULL DEFAULT 'cash' AFTER `amount_paid`,
  ADD COLUMN `bank_id` INT DEFAULT NULL AFTER `payment_method`;

-- Also store lat/lng separately for easier querying (POINT type is hard to query)
ALTER TABLE `Customers`
  ADD COLUMN `lat` DECIMAL(10,7) DEFAULT NULL AFTER `address`,
  ADD COLUMN `lng` DECIMAL(10,7) DEFAULT NULL AFTER `lat`;
