-- ============================================================
-- Schema Update 2 — Company Profile + Loan Dates
-- Run this ONCE on your existing database
-- ============================================================

-- 1. Company Profile table (stores logo, name, address, etc.)
CREATE TABLE IF NOT EXISTS `CompanyProfile` (
  `id`           INT NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(200) NOT NULL DEFAULT 'VertexSo Invest',
  `address`      TEXT DEFAULT NULL,
  `contact`      VARCHAR(30) DEFAULT NULL,
  `email`        VARCHAR(100) DEFAULT NULL,
  `reg_no`       VARCHAR(50) DEFAULT NULL,
  `logo_file`    VARCHAR(255) DEFAULT NULL COMMENT 'filename under /assets/',
  `updated_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default row if not exists
INSERT IGNORE INTO `CompanyProfile` (`id`, `company_name`) VALUES (1, 'VertexSo Invest');

-- 2. Add issue_date and end_date to Loans table
ALTER TABLE `Loans`
  ADD COLUMN `issue_date` DATE NULL AFTER `created_at`,
  ADD COLUMN `end_date`   DATE NULL AFTER `issue_date`;

-- 3. Back-fill existing loans from created_at + duration
UPDATE `Loans` SET
  issue_date = DATE(`created_at`),
  end_date   = DATE_ADD(DATE(`created_at`), INTERVAL `duration` DAY)
WHERE issue_date IS NULL;

-- 4. Make NOT NULL now that data is filled
ALTER TABLE `Loans`
  MODIFY COLUMN `issue_date` DATE NOT NULL,
  MODIFY COLUMN `end_date`   DATE NOT NULL;

-- ============================================================
-- Verify
-- DESCRIBE Loans;
-- SELECT * FROM CompanyProfile;
-- ============================================================
