-- ============================================================
-- Campaign Assist — DB Schema v1 (Voter WhatsApp + Admin Portal)
-- Engine: MySQL 8 / MariaDB 10.4+
-- Design rule: everything scoped by campaign_id so this works
-- for one candidate now and many later (config, not rebuild).
-- ============================================================

-- NOTE: Database already created in cPanel as galaxyup_votelink_db.
-- Import this file directly into that database via phpMyAdmin
-- (select galaxyup_votelink_db on the left, then Import this file).

-- ------------------------------------------------------------
-- 1. CAMPAIGNS — one row per aspirant/candidate (tenant)
-- ------------------------------------------------------------
CREATE TABLE campaigns (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,           -- "Hon. Jane Doe for MP Kisumu Central"
    slug            VARCHAR(60) NOT NULL UNIQUE,      -- "jane-doe-kisumu" (used in URLs)
    position_sought VARCHAR(100) DEFAULT NULL,        -- "MP", "Governor", "MCA Ward X"
    constituency    VARCHAR(150) DEFAULT NULL,
    whatsapp_number VARCHAR(20) DEFAULT NULL,         -- Twilio number assigned to this campaign
    logo_path       VARCHAR(255) DEFAULT NULL,
    brand_color     VARCHAR(7) DEFAULT '#0d6efd',
    welcome_message TEXT DEFAULT NULL,
    status          ENUM('active','paused','archived') DEFAULT 'active',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 2. USERS — portal users (admin/comms team/viewer), per campaign
-- ------------------------------------------------------------
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    full_name       VARCHAR(100) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    phone           VARCHAR(20) DEFAULT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('super_admin','admin','comms','viewer') DEFAULT 'comms',
    is_active       TINYINT(1) DEFAULT 1,
    last_login_at   TIMESTAMP NULL DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 3. WARDS — admin-defined geography for tagging voters/issues
-- ------------------------------------------------------------
CREATE TABLE wards (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(100) NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_ward_per_campaign (campaign_id, name)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 4. VOTERS — anyone who has messaged the WhatsApp bot
-- ------------------------------------------------------------
CREATE TABLE voters (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    phone           VARCHAR(20) NOT NULL,              -- WhatsApp number, E.164 format
    full_name       VARCHAR(100) DEFAULT NULL,
    ward_id         INT UNSIGNED DEFAULT NULL,
    opted_in        TINYINT(1) DEFAULT 1,              -- consent to receive broadcasts
    language_pref   ENUM('en','sw') DEFAULT 'en',
    first_seen_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE SET NULL,
    UNIQUE KEY uniq_voter_per_campaign (campaign_id, phone)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 5. CONVERSATIONS — one row per inbound/outbound WhatsApp message
--    (this is your conversation log + RAG audit trail)
-- ------------------------------------------------------------
CREATE TABLE conversations (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    voter_id        INT UNSIGNED NOT NULL,
    direction       ENUM('inbound','outbound') NOT NULL,
    message_type    ENUM('text','menu_selection','broadcast','issue_report','fallback') DEFAULT 'text',
    message_body    TEXT NOT NULL,
    intent_tag      VARCHAR(50) DEFAULT NULL,          -- e.g. "manifesto_qa", "issue", "menu"
    twilio_sid      VARCHAR(64) DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (voter_id) REFERENCES voters(id) ON DELETE CASCADE,
    INDEX idx_conv_campaign_time (campaign_id, created_at)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 6. KNOWLEDGE_DOCS — manifesto/policy docs feeding the RAG
-- ------------------------------------------------------------
CREATE TABLE knowledge_docs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    title           VARCHAR(150) NOT NULL,             -- "Manifesto 2027", "Bio", "Education Policy"
    file_path       VARCHAR(255) DEFAULT NULL,         -- original uploaded file, if any
    content         MEDIUMTEXT NOT NULL,               -- extracted/chunked text used for retrieval
    is_active       TINYINT(1) DEFAULT 1,
    uploaded_by     INT UNSIGNED DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 7. BROADCASTS — messages sent from portal to voter segments
-- ------------------------------------------------------------
CREATE TABLE broadcasts (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    title           VARCHAR(150) NOT NULL,
    message_body    TEXT NOT NULL,
    target_ward_id  INT UNSIGNED DEFAULT NULL,         -- NULL = all wards
    status          ENUM('draft','sending','sent','failed') DEFAULT 'draft',
    recipient_count INT UNSIGNED DEFAULT 0,
    sent_count      INT UNSIGNED DEFAULT 0,
    created_by      INT UNSIGNED DEFAULT NULL,
    scheduled_at    TIMESTAMP NULL DEFAULT NULL,
    sent_at         TIMESTAMP NULL DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (target_ward_id) REFERENCES wards(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 8. ISSUES — voter-submitted complaints/concerns, tagged by ward
-- ------------------------------------------------------------
CREATE TABLE issues (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     INT UNSIGNED NOT NULL,
    voter_id        INT UNSIGNED NOT NULL,
    ward_id         INT UNSIGNED DEFAULT NULL,
    description     TEXT NOT NULL,
    category        VARCHAR(50) DEFAULT NULL,          -- "infrastructure","health","security", etc.
    status          ENUM('new','in_review','resolved','dismissed') DEFAULT 'new',
    resolved_by     INT UNSIGNED DEFAULT NULL,
    resolved_at     TIMESTAMP NULL DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (voter_id) REFERENCES voters(id) ON DELETE CASCADE,
    FOREIGN KEY (ward_id) REFERENCES wards(id) ON DELETE SET NULL,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_issues_status (campaign_id, status)
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- 9. BOT_SESSIONS — tracks where a voter is in the WhatsApp menu flow
--    (lightweight state machine, avoids re-asking name/ward etc.)
-- ------------------------------------------------------------
CREATE TABLE bot_sessions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voter_id        INT UNSIGNED NOT NULL UNIQUE,
    current_state   VARCHAR(50) DEFAULT 'menu',        -- "awaiting_name","awaiting_ward","menu","qa","issue_capture"
    context_data    JSON DEFAULT NULL,                 -- scratch data for multi-step flows
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (voter_id) REFERENCES voters(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Seed: one campaign so you can start wiring the bot immediately
-- ------------------------------------------------------------
INSERT INTO campaigns (name, slug, position_sought, constituency, welcome_message)
VALUES ('Demo Candidate 2027', 'demo-candidate', 'MP', 'Demo Constituency',
'Karibu! I am the digital assistant for [Candidate Name]. Reply with a number:
1. Ask about our manifesto
2. Upcoming events
3. Report an issue in your area
4. Talk to our team');