-- Table for account types (Current, Savings, Salary Transfer, etc.)
CREATE TABLE `accounttypes` (
  `idaccounttype` int NOT NULL AUTO_INCREMENT,
  `accounttypecode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accounttypedesc` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accounttypeDetails` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `useascategory` tinyint(1) DEFAULT NULL,
  `menuitem` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`idaccounttype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Main bank accounts table
CREATE TABLE `bankaccounts` (
  `idbankaccount` int NOT NULL AUTO_INCREMENT,
  `accountname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accountdetails` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `idaccounttype` int NOT NULL,
  `idbank` int NOT NULL,
  `MinimumBalance` decimal(10,0) DEFAULT NULL,
  `MinimumIncome` decimal(10,0) DEFAULT NULL,
  `accounturl` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `accountimageid` varchar(45) DEFAULT NULL,
  `accountimagelocation` varchar(45) DEFAULT NULL,
  `accountimagename` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`idbankaccount`),
  KEY `fk_bankaccounts_accounttype_idx` (`idaccounttype`),
  KEY `fk_bankaccounts_bank_idx` (`idbank`),
  CONSTRAINT `fk_bankaccounts_accounttype` FOREIGN KEY (`idaccounttype`) REFERENCES `accounttypes` (`idaccounttype`),
  CONSTRAINT `fk_bankaccounts_bank` FOREIGN KEY (`idbank`) REFERENCES `banks` (`idbanks`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Features table for bank accounts
CREATE TABLE `bankaccountfeatures` (
  `idbankaccountfeature` int NOT NULL AUTO_INCREMENT,
  `bankaccountfeatureDetails` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Detailed description of the feature',
  `idbankaccount` int DEFAULT NULL,
  `bankaccountshortfeature` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Short headline feature (e.g., "Zero Balance", "Free Debit Card")',
  PRIMARY KEY (`idbankaccountfeature`),
  KEY `fk_bankaccountfeatures_bankaccount_idx` (`idbankaccount`),
  CONSTRAINT `fk_bankaccountfeatures_bankaccount` FOREIGN KEY (`idbankaccount`) REFERENCES `bankaccounts` (`idbankaccount`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `bankaccountoffers` (
  `idbankaccountoffer` int NOT NULL AUTO_INCREMENT,
  `bankaccountofferDetails` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Description of the current offer or promotion on the account',
  `idbankaccount` int DEFAULT NULL,
  PRIMARY KEY (`idbankaccountoffer`),
  KEY `fk_bankaccountoffers_bankaccount_idx` (`idbankaccount`),
  CONSTRAINT `fk_bankaccountoffers_bankaccount` FOREIGN KEY (`idbankaccount`) REFERENCES `bankaccounts` (`idbankaccount`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample data for accounttypes
INSERT INTO `accounttypes` (`accounttypecode`, `accounttypedesc`, `accounttypeDetails`, `useascategory`, `menuitem`) VALUES
('CUR', 'Current Account', 'Standard current account', 1, 'Current Account'),
('SAV', 'Savings Account', 'Savings account with interest', 1, 'Savings Account'),
('SAL', 'Salary Transfer Account', 'Account for salary transfer', 1, 'Salary Transfer Account'),
('ZER', 'Zero Balance Account', 'No minimum balance required', 1, 'Zero Balance Account'),
('DIG', 'Digital Account', 'Fully digital account', 1, 'Digital Account');

-- Sample data for bankaccounts (assuming idbank 4 = Ajman Bank, 8 = CBD, 12 = Emirates NBD)
INSERT INTO `bankaccounts` (`accountname`, `accountdetails`, `idaccounttype`, `idbank`, `MinimumBalance`, `MinimumIncome`, `accounturl`, `accountimageid`, `accountimagelocation`, `accountimagename`) VALUES
('Ajman Bank Current Account', 'A flexible current account for daily banking needs.', 1, 4, 3000, NULL, 'https://ajmanbank.ae/current', NULL, NULL, 'ajman_current.webp'),
('CBD Savings Account', 'Earn interest on your savings with easy access.', 2, 8, 0, NULL, 'https://cbd.ae/savings', NULL, NULL, 'cbd_savings.webp'),
('ENBD Salary Transfer Account', 'Special benefits for salary transfer customers.', 3, 12, 0, 5000, 'https://emiratesnbd.com/salary', NULL, NULL, 'enbd_salary.webp'),
('Ajman Bank Zero Balance Account', 'No minimum balance required, ideal for new customers.', 4, 4, 0, NULL, 'https://ajmanbank.ae/zero', NULL, NULL, 'ajman_zero.webp'),
('CBD Digital Account', 'Open and manage your account fully online.', 5, 8, 0, NULL, 'https://cbd.ae/digital', NULL, NULL, 'cbd_digital.webp');

-- Sample data for bankaccountfeatures
INSERT INTO `bankaccountfeatures` (`bankaccountfeatureDetails`, `idbankaccount`, `bankaccountshortfeature`) VALUES
('Free cheque book on account opening', 1, 'Free Cheque Book'),
('Earn up to 1.5% interest per annum', 2, 'High Interest'),
('Free debit card with unlimited ATM withdrawals', 3, 'Free Debit Card'),
('No minimum balance penalty', 4, 'Zero Balance'),
('Instant account opening via mobile app', 5, 'Instant Opening');

-- Sample data for bankaccountoffers
INSERT INTO `bankaccountoffers` (`bankaccountofferDetails`, `idbankaccount`) VALUES
('Get AED 200 cashback on first salary transfer', 3),
('Win an iPhone on opening a digital account this month', 5),
('Free movie tickets for 3 months', 2);