CREATE TABLE IF NOT EXISTS roles (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE NOT NULL);
CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, role_id INT REFERENCES roles(id), name VARCHAR(150) NOT NULL, email VARCHAR(180) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, status VARCHAR(30) DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS actors (id SERIAL PRIMARY KEY, name VARCHAR(200), actor_type VARCHAR(80), value_chain VARCHAR(120), province VARCHAR(120), commune VARCHAR(120), email VARCHAR(180), phone VARCHAR(60), status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS calls (id SERIAL PRIMARY KEY, reference VARCHAR(80), title VARCHAR(255), value_chains VARCHAR(255), budget NUMERIC(18,2) DEFAULT 0, open_date DATE, close_date DATE, status VARCHAR(60), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS applications (id SERIAL PRIMARY KEY, reference VARCHAR(80), operator_name VARCHAR(200), project_title VARCHAR(255), value_chain VARCHAR(120), infrastructure_type VARCHAR(160), province VARCHAR(120), total_budget NUMERIC(18,2) DEFAULT 0, requested_amount NUMERIC(18,2) DEFAULT 0, own_contribution NUMERIC(18,2) DEFAULT 0, status VARCHAR(60), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS evaluations (id SERIAL PRIMARY KEY, application_reference VARCHAR(80), evaluation_type VARCHAR(80), evaluator_name VARCHAR(150), score NUMERIC(5,2), recommendation VARCHAR(120), comments TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS committee_sessions (id SERIAL PRIMARY KEY, title VARCHAR(200), session_date DATE, chair_name VARCHAR(150), applications_count INT DEFAULT 0, status VARCHAR(60), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS contracts (id SERIAL PRIMARY KEY, application_reference VARCHAR(80), contract_reference VARCHAR(80), operator_name VARCHAR(200), amount NUMERIC(18,2), status VARCHAR(60), signed_at DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS disbursement_requests (id SERIAL PRIMARY KEY, contract_reference VARCHAR(80), tranche_no INT, amount NUMERIC(18,2), status VARCHAR(60), requested_at DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS complaints (id SERIAL PRIMARY KEY, reference VARCHAR(80), complainant VARCHAR(180), category VARCHAR(120), channel VARCHAR(80), status VARCHAR(60), description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS es_incidents (id SERIAL PRIMARY KEY, reference VARCHAR(80), project_reference VARCHAR(80), severity VARCHAR(60), status VARCHAR(60), description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS documents (id SERIAL PRIMARY KEY, title VARCHAR(220), document_type VARCHAR(100), module VARCHAR(100), version VARCHAR(30) DEFAULT '1.0', file_path VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS audit_logs (id BIGSERIAL PRIMARY KEY, user_id INT, action VARCHAR(180), module VARCHAR(100), ip_address VARCHAR(80), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO roles(name, slug) VALUES ('Super Administrateur','superadmin'),('Administrateur UGP','ugp_admin'),('Cabinet Assistance Technique','cabinet'),('Évaluateur','evaluator'),('Comité de sélection','committee'),('Observateur Banque mondiale','observer'),('Opérateur candidat','operator') ON CONFLICT DO NOTHING;
INSERT INTO users(role_id,name,email,password_hash,status) VALUES (1,'Administrateur PRETE','admin@prete.bi','$2y$10$66S95YY1m/HDNK8HJkqlbOGPVxEfwRb8nUMnlxxSXly.d7iOFAU.2','active') ON CONFLICT DO NOTHING;