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

CREATE TABLE roles(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) UNIQUE NOT NULL,label VARCHAR(100) NOT NULL);
INSERT INTO roles(name,label) VALUES ('citizen','Citizen'),('super_admin','Super Administrator'),('mda_admin','MDA Administrator'),('application_officer','Application Officer'),('finance_officer','Finance Officer'),('certificate_officer','Certificate Officer'),('auditor','Auditor');

CREATE TABLE users(id BIGINT AUTO_INCREMENT PRIMARY KEY,role_id INT NOT NULL,name VARCHAR(150) NOT NULL,email VARCHAR(190) UNIQUE NOT NULL,phone VARCHAR(30),password VARCHAR(255) NOT NULL,is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(role_id) REFERENCES roles(id));
INSERT INTO users(role_id,name,email,phone,password) VALUES
((SELECT id FROM roles WHERE name='super_admin'),'System Administrator','admin@example.gov.ng','08000000001','$2y$12$3NbukR9/W3yBoN5dYn2lquW66.ex04UTm1roO57IGVQ24xGDczGbi'),
((SELECT id FROM roles WHERE name='citizen'),'Demo Citizen','citizen@example.com','08000000002','$2y$12$lgTOgBUFpRd8kBgUD29yHu8Zs7hQ7C3q4OUMEzohbeWU0roHW7e5C');

CREATE TABLE citizen_profiles(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNIQUE NOT NULL,date_of_birth DATE,gender VARCHAR(20),address TEXT,id_type VARCHAR(50),id_number VARCHAR(100),nin VARCHAR(20),updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id));

CREATE TABLE forms(id BIGINT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,slug VARCHAR(190) UNIQUE NOT NULL,description TEXT,fee DECIMAL(14,2) DEFAULT 0,status ENUM('draft','published','archived') DEFAULT 'draft',processing_days INT DEFAULT 3,created_by BIGINT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(created_by) REFERENCES users(id));
CREATE TABLE form_fields(id BIGINT AUTO_INCREMENT PRIMARY KEY,form_id BIGINT NOT NULL,type VARCHAR(50) NOT NULL,label VARCHAR(190) NOT NULL,field_key VARCHAR(120) NOT NULL,help_text VARCHAR(255),is_required TINYINT(1) DEFAULT 0,options_json JSON,validation_json JSON,sort_order INT DEFAULT 0,FOREIGN KEY(form_id) REFERENCES forms(id) ON DELETE CASCADE);

CREATE TABLE applications(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,form_id BIGINT NOT NULL,reference VARCHAR(50) UNIQUE NOT NULL,status ENUM('draft','submitted','under_review','correction_required','approved','rejected','deleted') DEFAULT 'draft',submitted_at DATETIME NULL,deleted_at DATETIME NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(form_id) REFERENCES forms(id));
CREATE TABLE application_answers(id BIGINT AUTO_INCREMENT PRIMARY KEY,application_id BIGINT NOT NULL,form_field_id BIGINT NOT NULL,value_text LONGTEXT,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uq_answer(application_id,form_field_id),FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE,FOREIGN KEY(form_field_id) REFERENCES form_fields(id));
CREATE TABLE application_documents(id BIGINT AUTO_INCREMENT PRIMARY KEY,application_id BIGINT NOT NULL,form_field_id BIGINT NULL,original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120),file_size BIGINT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(application_id) REFERENCES applications(id),FOREIGN KEY(form_field_id) REFERENCES form_fields(id));

CREATE TABLE payment_accounts(id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,bank_name VARCHAR(150),account_number VARCHAR(50),revenue_code VARCHAR(100),is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE form_payment_splits(id BIGINT AUTO_INCREMENT PRIMARY KEY,form_id BIGINT NOT NULL,payment_account_id BIGINT NOT NULL,split_type ENUM('percentage','fixed') NOT NULL,split_value DECIMAL(14,2) NOT NULL,FOREIGN KEY(form_id) REFERENCES forms(id) ON DELETE CASCADE,FOREIGN KEY(payment_account_id) REFERENCES payment_accounts(id));
CREATE TABLE payments(id BIGINT AUTO_INCREMENT PRIMARY KEY,application_id BIGINT NOT NULL,payment_reference VARCHAR(100) UNIQUE NOT NULL,transaction_reference VARCHAR(150),receipt_number VARCHAR(100) UNIQUE,status ENUM('pending','successful','failed','reversed','duplicate') DEFAULT 'pending',amount DECIMAL(14,2) NOT NULL,provider VARCHAR(100),provider_payload JSON,paid_at DATETIME NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(application_id) REFERENCES applications(id));

CREATE TABLE certificates(id BIGINT AUTO_INCREMENT PRIMARY KEY,application_id BIGINT NOT NULL,certificate_number VARCHAR(100) UNIQUE NOT NULL,verification_code VARCHAR(100) UNIQUE NOT NULL,status ENUM('valid','expired','revoked','replaced') DEFAULT 'valid',file_path VARCHAR(255),signature_hash VARCHAR(255),issued_by BIGINT,issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,revoked_at DATETIME NULL,revocation_reason TEXT,FOREIGN KEY(application_id) REFERENCES applications(id),FOREIGN KEY(issued_by) REFERENCES users(id));

CREATE TABLE notifications(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,channel ENUM('in_app','email','sms') DEFAULT 'in_app',title VARCHAR(190),message TEXT NOT NULL,is_read TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE cms_pages(id BIGINT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(190) NOT NULL,slug VARCHAR(190) UNIQUE NOT NULL,body LONGTEXT,status ENUM('draft','published') DEFAULT 'draft',updated_by BIGINT,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(updated_by) REFERENCES users(id));
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id BIGINT NOT NULL,action VARCHAR(120) NOT NULL,entity_type VARCHAR(100) NOT NULL,entity_id BIGINT NULL,old_values JSON NULL,new_values JSON NULL,reason TEXT,ip_address VARCHAR(64),user_agent VARCHAR(255),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_audit_entity(entity_type,entity_id),INDEX idx_audit_time(created_at),FOREIGN KEY(user_id) REFERENCES users(id));

INSERT INTO forms(title,slug,description,fee,status,processing_days,created_by) VALUES ('Certificate of Origin','certificate-of-origin','Apply online for a Certificate of Origin.',5000,'published',3,1),('Pilgrimage Application','pilgrimage-application','Pilgrimage application and document submission.',10000,'published',5,1);
INSERT INTO form_fields(form_id,type,label,field_key,is_required,sort_order) VALUES
(1,'text','Local Government Area','lga',1,1),(1,'text','Town / Village','town',1,2),(1,'file_upload','Supporting Document','supporting_document',1,3),(1,'declaration','Declaration','declaration',1,4),
(2,'passport_photo','Passport Photograph','passport_photo',1,1),(2,'nin','NIN','nin',1,2),(2,'date','Date of Birth','dob',1,3),(2,'file_upload','International Passport','international_passport',1,4);
INSERT INTO cms_pages(title,slug,body,status,updated_by) VALUES ('About','about','Welcome to the digital services portal.','published',1),('Frequently Asked Questions','faq','Applications can be saved as drafts and continued later.','published',1);