CREATE TABLE `loansdetails` (
  `idloan` int NOT NULL AUTO_INCREMENT,
  `loanname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `loandetails` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `idloantype` int NOT NULL,
  `idbank` int NOT NULL,
  `MinimumIncome` decimal(10,0) DEFAULT NULL,
  `loanurl` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `FlatInterestRate` decimal(5,2) DEFAULT NULL COMMENT 'Flat interest rate as percentage per annum (e.g., 5.00%)',
  `ReducingInterestRate` decimal(5,2) DEFAULT NULL COMMENT 'Reducing balance interest rate as percentage per annum',
  `AnnualFee` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`idloan`),
  KEY `fk_loans_loantype_idx` (`idloantype`),
  KEY `fk_loans_bank_idx` (`idbank`),
  CONSTRAINT `fk_loans_bank` FOREIGN KEY (`idbank`) REFERENCES `banks` (`idbanks`),
  CONSTRAINT `fk_loans_loantype` FOREIGN KEY (`idloantype`) REFERENCES `loantypes` (`idloantype`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loanbenefits` (
  `idloanbenefit` int NOT NULL AUTO_INCREMENT,
  `loanbenefitDetails` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Detailed description of the benefit (e.g., "No foreclosure charges after 6 months")',
  `idloan` int DEFAULT NULL,
  `loanbenefitCategory` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Category of benefit (e.g., "Prepayment", "Customer Service", "Approval Speed")',
  PRIMARY KEY (`idloanbenefit`),
  KEY `fk_loanbenefits_loan_idx` (`idloan`),
  CONSTRAINT `fk_loanbenefits_loan` FOREIGN KEY (`idloan`) REFERENCES `loansdetails` (`idloan`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2222 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `loanfeatures` (
  `idloanfeature` int NOT NULL AUTO_INCREMENT,
  `loanfeatureDetails` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Detailed description of the feature',
  `idloan` int DEFAULT NULL,
  `loanshortfeature` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Short headline feature (e.g., "Low Processing Fee", "Flexible Tenure")',
  PRIMARY KEY (`idloanfeature`),
  KEY `fk_loanfeatures_loan_idx` (`idloan`),
  CONSTRAINT `fk_loanfeatures_loan` FOREIGN KEY (`idloan`) REFERENCES `loansdetails` (`idloan`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1717 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loanoffers` (
  `idloanoffer` int NOT NULL AUTO_INCREMENT,
  `loanofferDetails` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Description of the current offer or promotion on the loan',
  `idloan` int DEFAULT NULL,
  PRIMARY KEY (`idloanoffer`),
  KEY `fk_loanoffers_loan_idx` (`idloan`),
  CONSTRAINT `fk_loanoffers_loan` FOREIGN KEY (`idloan`) REFERENCES `loansdetails` (`idloan`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `loantypes` (
  `idloantype` int NOT NULL AUTO_INCREMENT,
  `loantypecode` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `loantypedesc` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `loantypeDetails` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `useascategory` tinyint(1) DEFAULT NULL,
  `menuitem` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`idloantype`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;