CREATE DATABASE IF NOT EXISTS yovanta CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE yovanta;

CREATE TABLE users(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,
 role ENUM('youth','officer','admin','partner') NOT NULL DEFAULT 'youth',
 age TINYINT UNSIGNED NULL,district VARCHAR(80) NULL,education VARCHAR(100) NULL,
 skills JSON NULL,aspiration VARCHAR(500) NULL,phone VARCHAR(40) NULL,
 passport_id VARCHAR(40) UNIQUE NULL,active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(role),INDEX(district)
);

CREATE TABLE opportunities(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,provider VARCHAR(180) NOT NULL,
 type VARCHAR(60) NOT NULL,district VARCHAR(100) DEFAULT 'All Uganda',description TEXT,tags VARCHAR(500),
 fit_score INT DEFAULT 50,active TINYINT(1) DEFAULT 1,closing_date DATE NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(type),INDEX(active)
);

CREATE TABLE applications(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,opportunity_id BIGINT UNSIGNED NOT NULL,
 status ENUM('Submitted','Review','Approved','Declined','Completed') DEFAULT 'Submitted',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY one_application(user_id,opportunity_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE
);

CREATE TABLE mentors(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120),sector VARCHAR(100),location VARCHAR(100),
 bio TEXT,capacity INT DEFAULT 5,verified TINYINT(1) DEFAULT 0,active TINYINT(1) DEFAULT 1
);
CREATE TABLE mentor_requests(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED,mentor_id BIGINT UNSIGNED,
 message VARCHAR(500),status VARCHAR(30) DEFAULT 'Pending',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(mentor_id) REFERENCES mentors(id) ON DELETE CASCADE
);

CREATE TABLE enterprises(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,impact_id VARCHAR(40) UNIQUE NOT NULL,owner_user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL,district VARCHAR(80),sector VARCHAR(80),members INT DEFAULT 1,jobs INT DEFAULT 0,
 status VARCHAR(40) DEFAULT 'Active',revenue_band VARCHAR(40) DEFAULT 'New',risk_score INT DEFAULT 10,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE CASCADE,INDEX(risk_score),INDEX(district)
);
CREATE TABLE enterprise_checkins(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,enterprise_id BIGINT UNSIGNED NOT NULL,notes TEXT,revenue_band VARCHAR(40),
 customers_count INT DEFAULT 0,issue VARCHAR(500),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(enterprise_id) REFERENCES enterprises(id) ON DELETE CASCADE
);
CREATE TABLE audit_logs(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NULL,action VARCHAR(100),entity_type VARCHAR(80),
 entity_id BIGINT NULL,ip VARCHAR(80),details JSON NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(action),INDEX(created_at)
);

INSERT INTO opportunities(title,provider,type,district,description,tags,fit_score) VALUES
('YLP Enterprise Pathway','MGLSD / Youth Livelihood Programme','Enterprise','All Uganda','Structured pathway for eligible youth groups seeking livelihood enterprise support.','enterprise,finance,mentorship',96),
('Digital Skills & Apprenticeship','Industry Partner','Training + Work','Kampala & Wakiso','Practical digital skills followed by employer-linked apprenticeship opportunities.','digital,job,apprenticeship',91),
('Youth Mentor Grid','YOVANTA Uganda','Mentorship','National / Online','Match with an experienced professional aligned to your sector and goals.','mentor,business',88),
('Green Enterprise Challenge','Partner Ecosystem','Innovation','Eastern & Northern Uganda','Prototype solutions for climate-smart agriculture and circular economy.','innovation,agriculture,green',79),
('TVET Skills Bridge','Skills Partner','Training','All Uganda','Find practical technical training and certification pathways.','skills,TVET',84);

INSERT INTO mentors(name,sector,location,bio,capacity,verified) VALUES
('Grace N.','Agribusiness','Mbarara','Enterprise mentor with an agribusiness focus.',8,1),
('David K.','Software & Digital','Kampala','Digital product and technology mentor.',12,1),
('Sarah A.','Fashion & Creative','Kampala','Creative enterprise and fashion mentor.',6,1),
('John O.','Renewable Energy','Gulu','Solar and renewable-energy enterprise mentor.',5,1);