-- =====================================================================
-- Military Meeting Management System (MMS) - Database Schema (SQLite)
-- Phase 1: Core meeting lifecycle, RBAC, audit trail.
-- =====================================================================

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------
-- RBAC: roles & permissions
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,          -- e.g. System Administrator
    slug VARCHAR(50) NOT NULL UNIQUE,          -- e.g. admin
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,         -- e.g. meetings.create
    description TEXT
);

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INTEGER NOT NULL,
    permission_id INTEGER NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------
-- Departments & Ranks
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    code VARCHAR(20) UNIQUE,
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ranks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    abbreviation VARCHAR(20),
    hierarchy_level INTEGER NOT NULL DEFAULT 0, -- lower = more senior
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ---------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_number VARCHAR(50) UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(30),
    password_hash VARCHAR(255) NOT NULL,
    role_id INTEGER NOT NULL,
    department_id INTEGER,
    rank_id INTEGER,
    avatar VARCHAR(255),
    is_active INTEGER NOT NULL DEFAULT 1,
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until DATETIME,
    last_login_at DATETIME,
    must_change_password INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (rank_id) REFERENCES ranks(id)
);

CREATE INDEX IF NOT EXISTS idx_users_role ON users(role_id);
CREATE INDEX IF NOT EXISTS idx_users_dept ON users(department_id);

-- ---------------------------------------------------------------------
-- Meeting categories & types
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS meeting_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ---------------------------------------------------------------------
-- Buildings & Rooms (kept lightweight in phase 1, feeds room booking later)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS buildings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS rooms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    building_id INTEGER,
    name VARCHAR(100) NOT NULL,
    capacity INTEGER,
    FOREIGN KEY (building_id) REFERENCES buildings(id)
);

-- ---------------------------------------------------------------------
-- Meetings
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meetings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_number VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    meeting_type_id INTEGER,
    meeting_category_id INTEGER,
    department_id INTEGER,
    description TEXT,
    meeting_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    venue VARCHAR(150),
    building_id INTEGER,
    room_id INTEGER,
    chairperson_id INTEGER,
    secretary_id INTEGER,
    status VARCHAR(30) NOT NULL DEFAULT 'scheduled', -- scheduled, in_progress, completed, cancelled, postponed
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',   -- low, normal, high, urgent
    classification VARCHAR(30) NOT NULL DEFAULT 'unclassified', -- unclassified, restricted, confidential, secret
    visibility VARCHAR(20) NOT NULL DEFAULT 'private', -- private, department, public
    is_recurring INTEGER NOT NULL DEFAULT 0,
    recurrence_rule VARCHAR(255),                     -- e.g. FREQ=WEEKLY;INTERVAL=1
    parent_meeting_id INTEGER,                         -- for recurring instances
    approval_status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, pending_secretary, pending_chair, pending_commander, published, rejected
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_type_id) REFERENCES meeting_types(id),
    FOREIGN KEY (meeting_category_id) REFERENCES meeting_categories(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (building_id) REFERENCES buildings(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    FOREIGN KEY (chairperson_id) REFERENCES users(id),
    FOREIGN KEY (secretary_id) REFERENCES users(id),
    FOREIGN KEY (parent_meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_meetings_date ON meetings(meeting_date);
CREATE INDEX IF NOT EXISTS idx_meetings_status ON meetings(status);
CREATE INDEX IF NOT EXISTS idx_meetings_dept ON meetings(department_id);

CREATE TABLE IF NOT EXISTS meeting_attachments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size INTEGER,
    uploaded_by INTEGER,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ---------------------------------------------------------------------
-- Agenda
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS agenda_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    topic VARCHAR(255) NOT NULL,
    speaker_id INTEGER,
    time_allocated_minutes INTEGER,
    notes TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, discussed, deferred, skipped
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (speaker_id) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_agenda_meeting ON agenda_items(meeting_id);

-- ---------------------------------------------------------------------
-- Participants & Attendance
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_participants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    invitation_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, accepted, declined
    is_required INTEGER NOT NULL DEFAULT 1,
    reminder_sent INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(meeting_id, user_id)
);

CREATE TABLE IF NOT EXISTS attendance (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'absent', -- present, absent, late, excused
    check_in_time DATETIME,
    method VARCHAR(20) NOT NULL DEFAULT 'manual', -- manual, qr, barcode, signature
    signature_path VARCHAR(255),
    recorded_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id),
    UNIQUE(meeting_id, user_id)
);

-- ---------------------------------------------------------------------
-- Minutes
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS minutes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL UNIQUE,
    content TEXT,                 -- rich text (HTML) - discussion narrative
    recommendations TEXT,
    notes TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft, submitted, approved, rejected, published
    prepared_by INTEGER,
    approved_by INTEGER,
    approved_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (prepared_by) REFERENCES users(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

-- ---------------------------------------------------------------------
-- Decision Register
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS decisions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    decision_number VARCHAR(50) NOT NULL UNIQUE,
    meeting_id INTEGER NOT NULL,
    agenda_item_id INTEGER,
    decision_text TEXT NOT NULL,
    responsible_department_id INTEGER,
    responsible_officer_id INTEGER,
    due_date DATE,
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'open', -- open, in_progress, completed, cancelled
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (agenda_item_id) REFERENCES agenda_items(id),
    FOREIGN KEY (responsible_department_id) REFERENCES departments(id),
    FOREIGN KEY (responsible_officer_id) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_decisions_meeting ON decisions(meeting_id);
CREATE INDEX IF NOT EXISTS idx_decisions_status ON decisions(status);

-- ---------------------------------------------------------------------
-- Action Items
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS action_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action_number VARCHAR(50) NOT NULL UNIQUE,
    meeting_id INTEGER,
    decision_id INTEGER,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    assigned_to INTEGER,
    assigned_by INTEGER,
    department_id INTEGER,
    progress_percent INTEGER NOT NULL DEFAULT 0,
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, cancelled, overdue
    deadline DATE,
    completed_at DATETIME,
    approved_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (decision_id) REFERENCES decisions(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    FOREIGN KEY (assigned_by) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (approved_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_action_items_status ON action_items(status);
CREATE INDEX IF NOT EXISTS idx_action_items_assignee ON action_items(assigned_to);

CREATE TABLE IF NOT EXISTS action_item_comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action_item_id INTEGER NOT NULL,
    user_id INTEGER,
    comment TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (action_item_id) REFERENCES action_items(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS action_item_evidence (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action_item_id INTEGER NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_by INTEGER,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (action_item_id) REFERENCES action_items(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ---------------------------------------------------------------------
-- Notifications
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title VARCHAR(150) NOT NULL,
    message TEXT,
    link VARCHAR(255),
    is_read INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read);

-- ---------------------------------------------------------------------
-- Audit Log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    action VARCHAR(100) NOT NULL,      -- login, logout, create, update, delete, approve, download, upload, print
    module VARCHAR(100),               -- meetings, minutes, decisions, action_items, users...
    record_id INTEGER,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_logs(created_at);

-- ---------------------------------------------------------------------
-- Login attempts (separate from audit for fast lockout checks)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(50),
    ip_address VARCHAR(45),
    success INTEGER NOT NULL DEFAULT 0,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================================
-- PHASE 2 / 3 MODULES
-- =====================================================================

-- ---------------------------------------------------------------------
-- Voting Module
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS votes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    meeting_id INTEGER NOT NULL,
    agenda_item_id INTEGER,
    question VARCHAR(500) NOT NULL,
    vote_type VARCHAR(20) NOT NULL DEFAULT 'open',   -- open, closed (closed = results hidden until closed)
    is_anonymous INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'open',       -- open, closed
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
    FOREIGN KEY (agenda_item_id) REFERENCES agenda_items(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS vote_responses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vote_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    choice VARCHAR(10) NOT NULL,   -- yes, no, abstain
    voted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vote_id) REFERENCES votes(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(vote_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_vote_responses_vote ON vote_responses(vote_id);

-- ---------------------------------------------------------------------
-- Document Management (folders, versioned files, permissions)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS document_folders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(150) NOT NULL,
    parent_id INTEGER,
    department_id INTEGER,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES document_folders(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    folder_id INTEGER,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    meeting_id INTEGER,
    current_version_id INTEGER,
    is_archived INTEGER NOT NULL DEFAULT 0,
    visibility VARCHAR(20) NOT NULL DEFAULT 'department', -- private, department, public
    department_id INTEGER,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (folder_id) REFERENCES document_folders(id) ON DELETE SET NULL,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS document_versions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id INTEGER NOT NULL,
    version_number INTEGER NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size INTEGER,
    file_type VARCHAR(20),
    change_notes TEXT,
    uploaded_by INTEGER,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_doc_versions_doc ON document_versions(document_id);

-- ---------------------------------------------------------------------
-- Resource Booking (rooms already exist; add projectors & vehicles as resources)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS resources (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(150) NOT NULL,
    type VARCHAR(20) NOT NULL,   -- projector, vehicle, other
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS resource_bookings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_id INTEGER NOT NULL,
    meeting_id INTEGER,
    booked_by INTEGER,
    purpose VARCHAR(255),
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'confirmed', -- confirmed, cancelled
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id) ON DELETE SET NULL,
    FOREIGN KEY (booked_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_resource_bookings_date ON resource_bookings(resource_id, booking_date);

-- ---------------------------------------------------------------------
-- Meeting Templates (for one-click cloning / recurring setup)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(150) NOT NULL,
    meeting_type_id INTEGER,
    meeting_category_id INTEGER,
    department_id INTEGER,
    title_template VARCHAR(255),
    description TEXT,
    default_duration_minutes INTEGER NOT NULL DEFAULT 60,
    default_venue VARCHAR(150),
    agenda_template TEXT,   -- newline-separated agenda topics applied on use
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_type_id) REFERENCES meeting_types(id),
    FOREIGN KEY (meeting_category_id) REFERENCES meeting_categories(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ---------------------------------------------------------------------
-- Registers: Risk, Issue, Lessons Learned (share a common shape)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS risk_register (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    risk_number VARCHAR(50) NOT NULL UNIQUE,
    meeting_id INTEGER,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    likelihood VARCHAR(20) NOT NULL DEFAULT 'medium', -- low, medium, high
    impact VARCHAR(20) NOT NULL DEFAULT 'medium',      -- low, medium, high
    mitigation_plan TEXT,
    owner_id INTEGER,
    department_id INTEGER,
    status VARCHAR(20) NOT NULL DEFAULT 'open',        -- open, mitigated, closed, escalated
    identified_date DATE,
    review_date DATE,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (owner_id) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS issue_register (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    issue_number VARCHAR(50) NOT NULL UNIQUE,
    meeting_id INTEGER,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    raised_by INTEGER,
    assigned_to INTEGER,
    department_id INTEGER,
    status VARCHAR(20) NOT NULL DEFAULT 'open',        -- open, in_progress, resolved, closed
    resolution TEXT,
    due_date DATE,
    resolved_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (raised_by) REFERENCES users(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE IF NOT EXISTS lessons_learned (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    lesson_number VARCHAR(50) NOT NULL UNIQUE,
    meeting_id INTEGER,
    title VARCHAR(255) NOT NULL,
    context TEXT,
    what_happened TEXT,
    recommendation TEXT,
    category VARCHAR(100),
    department_id INTEGER,
    submitted_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (submitted_by) REFERENCES users(id)
);

-- ---------------------------------------------------------------------
-- Outbound message log (email/SMS delivery record)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS message_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    channel VARCHAR(10) NOT NULL,      -- email, sms
    recipient VARCHAR(150) NOT NULL,
    subject VARCHAR(255),
    body TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, sent, failed
    error TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME
);

-- ---------------------------------------------------------------------
-- User preferences (theme, etc.)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_preferences (
    user_id INTEGER PRIMARY KEY,
    theme VARCHAR(10) NOT NULL DEFAULT 'light', -- light, dark
    email_notifications INTEGER NOT NULL DEFAULT 1,
    sms_notifications INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------
-- Built-in Video Calling (WebRTC signaling)
--
-- These two tables are the "switchboard" that lets two browsers find each
-- other and negotiate a direct peer-to-peer video connection. Once the call
-- connects, audio/video flows browser-to-browser and NEVER passes through
-- this server or these tables — only the initial handshake does.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS video_peers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room VARCHAR(100) NOT NULL,
    peer_id VARCHAR(64) NOT NULL UNIQUE,
    user_id INTEGER,
    display_name VARCHAR(150),
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_video_peers_room ON video_peers(room, last_seen);

CREATE TABLE IF NOT EXISTS video_signals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room VARCHAR(100) NOT NULL,
    from_peer VARCHAR(64) NOT NULL,
    to_peer VARCHAR(64),               -- NULL = broadcast to everyone in the room
    signal_type VARCHAR(20) NOT NULL,  -- offer, answer, ice, chat
    payload TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_video_signals_room ON video_signals(room, id);
