-- MCA Campaign & Election Operations - MySQL schema
-- Create any database name you choose, then import this file.
-- Example:
-- CREATE DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE your_database_name;

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 email_verified_at TIMESTAMP NULL,
 password VARCHAR(255) NOT NULL,
 role VARCHAR(50) NOT NULL DEFAULT 'campaign_admin',
 remember_token VARCHAR(100) NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 candidate_name VARCHAR(255) NOT NULL,
 ward_name VARCHAR(255) NOT NULL,
 constituency_name VARCHAR(255) NULL,
 county_name VARCHAR(255) NULL,
 status ENUM('planning','active','completed') NOT NULL DEFAULT 'planning',
 created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT fk_campaign_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS polling_stations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 code VARCHAR(100) NOT NULL UNIQUE,
 ward_name VARCHAR(255) NOT NULL,
 location VARCHAR(255) NULL,
 registered_voters INT UNSIGNED NOT NULL DEFAULT 0,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 phone VARCHAR(30) NULL,
 email VARCHAR(255) NULL,
 polling_station_id BIGINT UNSIGNED NULL,
 training_status ENUM('pending','completed') NOT NULL DEFAULT 'pending',
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT fk_agent_station FOREIGN KEY(polling_station_id) REFERENCES polling_stations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rehearsals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 scheduled_at DATETIME NULL,
 scenario ENUM('normal','connectivity_failure','delayed_sync','incident','result_discrepancy','correction') NOT NULL DEFAULT 'normal',
 status ENUM('active','closed','purged') NOT NULL DEFAULT 'active',
 created_by BIGINT UNSIGNED NULL,
 completed_at DATETIME NULL,
 purged_at DATETIME NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT fk_rehearsal_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS incidents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 polling_station_id BIGINT UNSIGNED NULL,
 category VARCHAR(100) NOT NULL,
 description TEXT NOT NULL,
 status ENUM('open','under_review','resolved','closed') NOT NULL DEFAULT 'open',
 mode ENUM('rehearsal','live') NOT NULL DEFAULT 'live',
 reported_by BIGINT UNSIGNED NULL,
 resolution TEXT NULL,
 client_submission_id CHAR(36) NULL UNIQUE,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT fk_incident_station FOREIGN KEY(polling_station_id) REFERENCES polling_stations(id) ON DELETE SET NULL,
 CONSTRAINT fk_incident_user FOREIGN KEY(reported_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS result_submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 polling_station_id BIGINT UNSIGNED NOT NULL,
 candidate_a_votes INT UNSIGNED NOT NULL DEFAULT 0,
 candidate_b_votes INT UNSIGNED NOT NULL DEFAULT 0,
 candidate_c_votes INT UNSIGNED NOT NULL DEFAULT 0,
 rejected_votes INT UNSIGNED NOT NULL DEFAULT 0,
 total_valid_votes INT UNSIGNED NOT NULL DEFAULT 0,
 mode ENUM('rehearsal','live') NOT NULL DEFAULT 'live',
 status ENUM('pending','submitted','review_required','reviewed') NOT NULL DEFAULT 'pending',
 submitted_by BIGINT UNSIGNED NULL,
 submitted_at DATETIME NULL,
 client_submission_id CHAR(36) NOT NULL UNIQUE,
 device_id VARCHAR(255) NULL,
 server_received_at DATETIME NULL,
 version INT UNSIGNED NOT NULL DEFAULT 1,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 review_notes TEXT NULL,
 notes TEXT NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 CONSTRAINT fk_result_station FOREIGN KEY(polling_station_id) REFERENCES polling_stations(id) ON DELETE CASCADE,
 CONSTRAINT fk_result_submitter FOREIGN KEY(submitted_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_result_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(255) NOT NULL,
 entity_type VARCHAR(255) NULL,
 entity_id BIGINT UNSIGNED NULL,
 metadata JSON NULL,
 ip_address VARCHAR(45) NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 INDEX idx_entity(entity_type,entity_id),
 CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;