CREATE DATABASE IF NOT EXISTS giif_infravest CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE giif_infravest;
CREATE TABLE IF NOT EXISTS investments(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200), sector VARCHAR(80), region VARCHAR(80), committed_amount DECIMAL(18,2) DEFAULT 0, current_value DECIMAL(18,2) DEFAULT 0, roi DECIMAL(6,2) DEFAULT 0, status ENUM('Pipeline','Active','Exited') DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS transactions(id INT AUTO_INCREMENT PRIMARY KEY, investment_id INT NOT NULL, tx_date DATE NOT NULL, type ENUM('Disbursement','Valuation','Dividend','Fee') NOT NULL, amount DECIMAL(18,2) NOT NULL, currency VARCHAR(8) DEFAULT 'USD', notes VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (investment_id) REFERENCES investments(id) ON DELETE CASCADE);
INSERT INTO investments(name,sector,region,committed_amount,current_value,roi,status) VALUES
('Kpone Thermal Expansion','Energy','Greater Accra',250000000,270000000,12.0,'Active'),
('Eastern Corridor Roads','Transport','Volta',180000000,175000000,8.5,'Active'),
('Tamale Water Expansion','Water','Northern',120000000,125000000,6.0,'Active'),
('National Fiber Backbone II','ICT','Multiple',90000000,98000000,11.4,'Active'),
('Airport Terminal Upgrade','Transport','Greater Accra',75000000,84000000,14.2,'Pipeline');
INSERT INTO transactions(investment_id,tx_date,type,amount,currency,notes) VALUES
(1,'2025-01-31','Valuation',268000000,'USD','EoM Valuation'),
(1,'2025-02-28','Valuation',269500000,'USD','EoM Valuation'),
(2,'2025-01-31','Valuation',173000000,'USD','EoM Valuation'),
(2,'2025-02-28','Valuation',174000000,'USD','EoM Valuation'),
(3,'2025-01-31','Valuation',123000000,'USD','EoM Valuation'),
(3,'2025-02-28','Valuation',123500000,'USD','EoM Valuation');