-- MySQL 8.0.16+ / 8.4 LTS. Run once on an empty database.
CREATE DATABASE IF NOT EXISTS jhasi_assessment CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jhasi_assessment;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','assessor','reviewer','distributor','meal') NOT NULL,
 location ENUM('zaatari','ghor') NULL,
 active BOOLEAN NOT NULL DEFAULT TRUE,
 must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
 session_version INT UNSIGNED NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CHECK ((role = 'distributor' AND location IS NOT NULL) OR (role <> 'distributor' AND location IS NULL))
) ENGINE=InnoDB;

CREATE TABLE assessments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 reference CHAR(17) NOT NULL UNIQUE,
 household_hash CHAR(64) NOT NULL UNIQUE,
 identity_hash CHAR(64) NOT NULL UNIQUE,
 booklet_hash CHAR(64) NOT NULL UNIQUE,
 assessor_id BIGINT UNSIGNED NOT NULL,
 location ENUM('zaatari','ghor') NOT NULL,
 status ENUM('submitted','eligible','ineligible','closed') NOT NULL DEFAULT 'submitted',
 contact_cipher MEDIUMTEXT NOT NULL,
 answers_cipher MEDIUMTEXT NOT NULL,
 score SMALLINT UNSIGNED NULL,
 priority ENUM('very_high','high','moderate','low') NULL,
 scoring_json JSON NOT NULL,
 scoring_version VARCHAR(100) NOT NULL,
 assessed_on DATE NOT NULL,
 submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_at DATETIME NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 closed_at DATETIME NULL,
 closed_by BIGINT UNSIGNED NULL,
 FOREIGN KEY (assessor_id) REFERENCES users(id),
 FOREIGN KEY (reviewed_by) REFERENCES users(id),
 FOREIGN KEY (closed_by) REFERENCES users(id),
 CHECK (score IS NULL OR score <= 100),
 INDEX queue (location, status, submitted_at),
 INDEX assessor_queue (assessor_id, submitted_at)
) ENGINE=InnoDB;

CREATE TABLE attachments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_id BIGINT UNSIGNED NOT NULL,
 kind ENUM('id_copy','family_booklet','receipt') NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 mime_type VARCHAR(80) NOT NULL,
 byte_size INT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL,
 uploaded_by BIGINT UNSIGNED NOT NULL,
 uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE (assessment_id, kind),
 FOREIGN KEY (assessment_id) REFERENCES assessments(id),
 FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE case_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 event_type ENUM('comment','eligible','ineligible','reopen','contact','closed','rescore') NOT NULL,
 body_cipher TEXT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (assessment_id) REFERENCES assessments(id),
 FOREIGN KEY (user_id) REFERENCES users(id),
 INDEX timeline (assessment_id, id)
) ENGINE=InnoDB;

CREATE TABLE audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(60) NOT NULL,
 assessment_id BIGINT UNSIGNED NULL,
 ip_hash CHAR(64) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX audit_time (created_at)
) ENGINE=InnoDB;

CREATE TABLE login_limits (
 bucket CHAR(64) PRIMARY KEY,
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 window_start DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Defence in depth: assessment facts are immutable after INSERT, even if a new API is added.
DELIMITER $$
CREATE TRIGGER immutable_assessment BEFORE UPDATE ON assessments FOR EACH ROW
BEGIN
 IF NOT (OLD.answers_cipher <=> NEW.answers_cipher)
 OR NOT (OLD.contact_cipher <=> NEW.contact_cipher)
 OR NOT (OLD.household_hash <=> NEW.household_hash)
 OR NOT (OLD.identity_hash <=> NEW.identity_hash)
 OR NOT (OLD.booklet_hash <=> NEW.booklet_hash)
 OR NOT (OLD.assessor_id <=> NEW.assessor_id)
 OR NOT (OLD.location <=> NEW.location)
 OR NOT (OLD.reference <=> NEW.reference)
 OR NOT (OLD.assessed_on <=> NEW.assessed_on)
 OR NOT (OLD.submitted_at <=> NEW.submitted_at) THEN
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Submitted assessment facts are immutable';
 END IF;
 IF OLD.status = 'closed' AND NEW.status <> 'closed' THEN
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Closed distributions are final';
 END IF;
END$$
DELIMITER ;

CREATE TABLE programme_settings (
 id TINYINT UNSIGNED PRIMARY KEY,
 opens TIME NOT NULL DEFAULT '09:00:00',
 closes TIME NOT NULL DEFAULT '15:00:00',
 assessment_fils INT UNSIGNED NOT NULL DEFAULT 2120,
 distribution_fils INT UNSIGNED NOT NULL DEFAULT 1420,
 version INT UNSIGNED NOT NULL DEFAULT 1,
 CHECK (id=1 AND opens < closes AND opens >= '00:00:00' AND closes < '24:00:00'),
 CHECK (assessment_fils <= 1000000 AND distribution_fils <= 1000000)
) ENGINE=InnoDB;
INSERT INTO programme_settings (id) VALUES (1);

CREATE TABLE settings_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 before_json JSON NOT NULL,
 after_json JSON NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE incentive_ledger (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 activity ENUM('assessment','distribution') NOT NULL,
 rate_fils INT UNSIGNED NOT NULL,
 occurred_at DATETIME NOT NULL,
 origin ENUM('live','legacy_backfill') NOT NULL DEFAULT 'live',
 UNIQUE (assessment_id,activity),
 INDEX staff_activity (user_id,occurred_at),
 FOREIGN KEY (assessment_id) REFERENCES assessments(id),
 FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
-- Initial agreed rates for work recorded before this upgrade; marked explicitly.
INSERT INTO incentive_ledger (assessment_id,user_id,activity,rate_fils,occurred_at,origin)
 SELECT id,assessor_id,'assessment',2120,submitted_at,'legacy_backfill' FROM assessments;
INSERT INTO incentive_ledger (assessment_id,user_id,activity,rate_fils,occurred_at,origin)
 SELECT a.id,a.closed_by,'distribution',1420,a.closed_at,'legacy_backfill' FROM assessments a
 WHERE a.status='closed' AND a.closed_by IS NOT NULL AND EXISTS (SELECT 1 FROM attachments f WHERE f.assessment_id=a.id AND f.kind='receipt');

CREATE TABLE quality_checks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 assessment_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 stage ENUM('assessment','distribution') NOT NULL,
 outcome ENUM('checked','follow_up') NOT NULL,
 body_cipher TEXT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (assessment_id) REFERENCES assessments(id),
 FOREIGN KEY (user_id) REFERENCES users(id),
 INDEX quality_timeline (assessment_id,stage,id)
) ENGINE=InnoDB;