-- Mission Control Dashboard Database Schema
-- PostgreSQL with Trigger + Optimistic Locking

-- ============================================
-- Main Dashboard Table
-- ============================================
CREATE TABLE IF NOT EXISTS mission_dashboard (
    device_id VARCHAR(64) PRIMARY KEY,
    version INTEGER NOT NULL DEFAULT 1,
    last_synced_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    todo_list JSONB DEFAULT '[]'::jsonb,
    mission_list JSONB DEFAULT '[]'::jsonb,
    done_list JSONB DEFAULT '[]'::jsonb,
    notes JSONB DEFAULT '[]'::jsonb,
    rules JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Index for faster queries
CREATE INDEX IF NOT EXISTS idx_mission_dashboard_version 
ON mission_dashboard(device_id, version);

-- ============================================
-- Mission Items Table
-- ============================================
CREATE TABLE IF NOT EXISTS mission_items (
    id UUID PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL REFERENCES mission_dashboard(device_id) ON DELETE CASCADE,
    list_type VARCHAR(32) NOT NULL DEFAULT 'todo', -- 'todo', 'mission', 'done'
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT '',
    priority INTEGER NOT NULL DEFAULT 2, -- 1=LOW, 2=MEDIUM, 3=HIGH, 4=CRITICAL
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    assigned_bot VARCHAR(64),
    eta TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_by VARCHAR(64) DEFAULT 'user',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_mission_items_device 
ON mission_items(device_id);

CREATE INDEX IF NOT EXISTS idx_mission_items_list_type 
ON mission_items(list_type);

CREATE INDEX IF NOT EXISTS idx_mission_items_status 
ON mission_items(status);

CREATE INDEX IF NOT EXISTS idx_mission_items_priority 
ON mission_items(priority DESC);

-- ============================================
-- Notes Table
-- ============================================
CREATE TABLE IF NOT EXISTS mission_notes (
    id VARCHAR(48) PRIMARY KEY DEFAULT ('note_' || encode(gen_random_bytes(12), 'hex')),
    device_id VARCHAR(64) NOT NULL REFERENCES mission_dashboard(device_id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(64) DEFAULT 'general',
    created_by VARCHAR(64) DEFAULT 'user',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_mission_notes_device 
ON mission_notes(device_id);

CREATE INDEX IF NOT EXISTS idx_mission_notes_category 
ON mission_notes(category);

-- ============================================
-- Rules Table
-- ============================================
CREATE TABLE IF NOT EXISTS mission_rules (
    id VARCHAR(48) PRIMARY KEY DEFAULT ('rule_' || encode(gen_random_bytes(12), 'hex')),
    device_id VARCHAR(64) NOT NULL REFERENCES mission_dashboard(device_id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    rule_type VARCHAR(64) NOT NULL, -- 'WORKFLOW', 'CODE_REVIEW', 'COMMUNICATION', 'DEPLOYMENT', 'SYNC'
    is_enabled BOOLEAN DEFAULT TRUE,
    priority INTEGER DEFAULT 0,
    config JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_mission_rules_device 
ON mission_rules(device_id);

CREATE INDEX IF NOT EXISTS idx_mission_rules_type 
ON mission_rules(rule_type);

-- ============================================
-- Sync Log Table (for conflict tracking)
-- ============================================
CREATE TABLE IF NOT EXISTS mission_sync_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id VARCHAR(64) NOT NULL,
    action VARCHAR(64) NOT NULL, -- 'CREATE', 'UPDATE', 'DELETE', 'SYNC'
    item_type VARCHAR(32), -- 'ITEM', 'NOTE', 'RULE'
    item_id VARCHAR(48),
    old_version INTEGER,
    new_version INTEGER,
    performed_by VARCHAR(64),
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_mission_sync_log_device 
ON mission_sync_log(device_id);

CREATE INDEX IF NOT EXISTS idx_mission_sync_log_timestamp 
ON mission_sync_log(timestamp DESC);

-- ============================================
-- Trigger Function: Auto-increment version
-- ============================================
CREATE OR REPLACE FUNCTION increment_dashboard_version()
RETURNS TRIGGER AS $$
BEGIN
    NEW.version = OLD.version + 1;
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Trigger: Dashboard version increment
-- ============================================
DROP TRIGGER IF EXISTS trg_mission_dashboard_version ON mission_dashboard;

CREATE TRIGGER trg_mission_dashboard_version
    BEFORE UPDATE ON mission_dashboard
    FOR EACH ROW
    EXECUTE FUNCTION increment_dashboard_version();

-- ============================================
-- Optimistic Lock Check Function
-- ============================================
CREATE OR REPLACE FUNCTION check_version_match(
    p_device_id VARCHAR(64),
    p_expected_version INTEGER
) RETURNS BOOLEAN AS $$
DECLARE
    v_current_version INTEGER;
BEGIN
    SELECT version INTO v_current_version 
    FROM mission_dashboard 
    WHERE device_id = p_device_id;
    
    RETURN v_current_version = p_expected_version;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Sync Helper Function
-- ============================================
CREATE OR REPLACE FUNCTION record_sync_action(
    p_device_id VARCHAR(64),
    p_action VARCHAR(64),
    p_item_type VARCHAR(32),
    p_item_id VARCHAR(48),
    p_old_version INTEGER,
    p_new_version INTEGER,
    p_performed_by VARCHAR(64)
) RETURNS VOID AS $$
BEGIN
    INSERT INTO mission_sync_log (
        device_id, action, item_type, item_id, 
        old_version, new_version, performed_by
    ) VALUES (
        p_device_id, p_action, p_item_type, p_item_id,
        p_old_version, p_new_version, p_performed_by
    );
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Initial Data Helper
-- ============================================
CREATE OR REPLACE FUNCTION init_mission_dashboard(
    p_device_id VARCHAR(64)
) RETURNS VOID AS $$
BEGIN
    INSERT INTO mission_dashboard (device_id)
    VALUES (p_device_id)
    ON CONFLICT (device_id) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Migration: Add skills column
-- ============================================
ALTER TABLE mission_dashboard ADD COLUMN IF NOT EXISTS skills JSONB DEFAULT '[]'::jsonb;

-- ============================================
-- Migration: Add souls column
-- ============================================
ALTER TABLE mission_dashboard ADD COLUMN IF NOT EXISTS souls JSONB DEFAULT '[]'::jsonb;

-- ============================================
-- Bot File Storage Table
-- ============================================
CREATE TABLE IF NOT EXISTS bot_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id VARCHAR(64) NOT NULL,
    entity_id INTEGER NOT NULL,
    filename VARCHAR(255) NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(device_id, entity_id, filename)
);

CREATE INDEX IF NOT EXISTS idx_bot_files_device_entity
ON bot_files(device_id, entity_id);

-- ============================================
-- Note Pages Table (Webview static pages for notes)
-- ============================================
CREATE TABLE IF NOT EXISTS note_pages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id VARCHAR(64) NOT NULL,
    note_id VARCHAR(128) NOT NULL,
    html_content TEXT NOT NULL DEFAULT '',
    drawing_data TEXT DEFAULT NULL,
    drawing_snapshot TEXT DEFAULT NULL,
    is_public BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(device_id, note_id)
);

CREATE INDEX IF NOT EXISTS idx_note_pages_device
ON note_pages(device_id);

-- Add is_public column for public page sharing (default false)
ALTER TABLE note_pages ADD COLUMN IF NOT EXISTS is_public BOOLEAN NOT NULL DEFAULT FALSE;

-- Add markdown_content column for Markdown-based pages
ALTER TABLE note_pages ADD COLUMN IF NOT EXISTS markdown_content TEXT DEFAULT NULL;

-- Add script_description column for consent-gated script execution
-- When a note page contains <script> tags, this field describes what the scripts do.
ALTER TABLE note_pages ADD COLUMN IF NOT EXISTS script_description TEXT DEFAULT NULL;

-- Page view tracking for visitor analytics
CREATE TABLE IF NOT EXISTS page_views (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL,
    note_id TEXT,
    public_code TEXT NOT NULL,
    visitor_ip TEXT,
    visitor_ua TEXT,
    referer TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_page_views_device ON page_views (device_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_page_views_note ON page_views (device_id, note_id, created_at DESC);

-- Custom domain mapping
CREATE TABLE IF NOT EXISTS custom_domains (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL,
    public_code TEXT NOT NULL,
    domain TEXT NOT NULL UNIQUE,
    verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_custom_domains_domain ON custom_domains (domain);
CREATE INDEX IF NOT EXISTS idx_custom_domains_device ON custom_domains (device_id);

-- Form submissions from public Note Pages
CREATE TABLE IF NOT EXISTS form_submissions (
    id SERIAL PRIMARY KEY,
    device_id TEXT NOT NULL,
    note_id TEXT NOT NULL,
    public_code TEXT NOT NULL,
    form_data JSONB NOT NULL DEFAULT '{}',
    visitor_ip TEXT,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_form_submissions_device ON form_submissions (device_id, created_at DESC);

-- Chat commerce orders
CREATE TABLE IF NOT EXISTS chat_orders (
    id SERIAL PRIMARY KEY,
    order_id TEXT UNIQUE NOT NULL DEFAULT ('ORD-' || to_char(NOW(), 'YYYYMMDD') || '-' || substr(md5(random()::text), 1, 6)),
    device_id TEXT NOT NULL,
    entity_id INTEGER,
    public_code TEXT,
    user_account_id TEXT,
    product_name TEXT NOT NULL,
    product_price NUMERIC(10,2),
    quantity INTEGER DEFAULT 1,
    shipping_name TEXT,
    shipping_phone TEXT,
    shipping_address TEXT,
    shipping_email TEXT,
    payment_status TEXT DEFAULT 'pending',
    payment_method TEXT,
    tappay_rec_trade_id TEXT,
    status TEXT DEFAULT 'created',
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_chat_orders_device ON chat_orders (device_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_chat_orders_status ON chat_orders (status);

-- Migration: add drawing_snapshot column for bot-readable PNG snapshots
ALTER TABLE note_pages ADD COLUMN IF NOT EXISTS drawing_snapshot TEXT DEFAULT NULL;

-- Grant execute to app user (adjust as needed)
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
-- GRANT ALL ON ALL TABLES IN SCHEMA public TO app_user;

-- ============================================
-- ID prefix migration (idempotent; no-ops after first run)
-- Widen mission_notes.id + mission_rules.id UUID → VARCHAR(48) so
-- Stripe-style prefixed IDs (note_<24hex>, rule_<24hex>) can coexist
-- with legacy UUIDs. These tables have no FK children.
-- ============================================
ALTER TABLE mission_notes ALTER COLUMN id DROP DEFAULT;
ALTER TABLE mission_notes ALTER COLUMN id TYPE VARCHAR(48) USING id::text;
ALTER TABLE mission_notes ALTER COLUMN id SET DEFAULT ('note_' || encode(gen_random_bytes(12), 'hex'));
ALTER TABLE mission_rules ALTER COLUMN id DROP DEFAULT;
ALTER TABLE mission_rules ALTER COLUMN id TYPE VARCHAR(48) USING id::text;
ALTER TABLE mission_rules ALTER COLUMN id SET DEFAULT ('rule_' || encode(gen_random_bytes(12), 'hex'));
ALTER TABLE mission_sync_log ALTER COLUMN item_id TYPE VARCHAR(48) USING item_id::text;

-- ============================================
-- Mission note ↔ Kanban card explicit links
-- Source of truth for first-class note_on_card graph edges.
-- Notes live in mission_dashboard.notes JSONB, so note_id is validated in
-- route code and cannot use a database FK. Cards are validated device-scoped
-- before insert/update.
-- ============================================
CREATE TABLE IF NOT EXISTS mission_note_card_links (
    id BIGSERIAL PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL REFERENCES mission_dashboard(device_id) ON DELETE CASCADE,
    note_id VARCHAR(48) NOT NULL,
    card_id VARCHAR(48) NOT NULL,
    created_by INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(device_id, note_id, card_id)
);

CREATE INDEX IF NOT EXISTS idx_mission_note_card_links_note
ON mission_note_card_links(device_id, note_id);

CREATE INDEX IF NOT EXISTS idx_mission_note_card_links_card
ON mission_note_card_links(device_id, card_id);
