-- Voxyz Autonomous Architecture - Phase 10 Database Schema
-- Creates tables for Proposal Service, Policy System, Trigger Rules, and Daily Missions

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================
-- 1. MISSION PROPOSALS TABLE
-- Stores AI-generated daily mission proposals
-- ============================================
CREATE TABLE IF NOT EXISTS ops_mission_proposals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Proposal metadata
    proposal_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' 
        CHECK (status IN ('pending', 'approved', 'rejected', 'scheduled', 'completed', 'expired')),
    
    -- Priority scoring (0-100)
    priority_score INTEGER NOT NULL DEFAULT 50 
        CHECK (priority_score >= 0 AND priority_score <= 100),
    
    -- Cap Gates compliance
    passes_cap_gates BOOLEAN NOT NULL DEFAULT false,
    cap_gate_violations JSONB DEFAULT '[]'::jsonb,
    
    -- Source task references (links to gantt tasks)
    source_task_ids TEXT[] DEFAULT '{}',
    source_project_ids TEXT[] DEFAULT '{}',
    
    -- Proposal content
    title TEXT NOT NULL,
    description TEXT,
    rationale TEXT,
    
    -- AI analysis summary
    ai_analysis JSONB DEFAULT '{}'::jsonb,
    
    -- Scheduling
    suggested_start_time TIMESTAMP WITH TIME ZONE,
    suggested_end_time TIMESTAMP WITH TIME ZONE,
    estimated_duration_minutes INTEGER,
    
    -- User decisions
    approved_at TIMESTAMP WITH TIME ZONE,
    approved_by UUID REFERENCES users(id),
    rejected_at TIMESTAMP WITH TIME ZONE,
    rejection_reason TEXT,
    
    -- Calendar integration
    calendar_event_id TEXT,
    calendar_synced_at TIMESTAMP WITH TIME ZONE,
    
    -- Execution tracking
    executed_at TIMESTAMP WITH TIME ZONE,
    execution_notes TEXT,
    
    -- Timestamps for lifecycle
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Indexes
    CONSTRAINT valid_dates CHECK (suggested_end_time > suggested_start_time OR suggested_start_time IS NULL)
);

-- Create indexes for mission_proposals
CREATE INDEX IF NOT EXISTS idx_proposals_status ON ops_mission_proposals(status);
CREATE INDEX IF NOT EXISTS idx_proposals_date ON ops_mission_proposals(proposal_date);
CREATE INDEX IF NOT EXISTS idx_proposals_priority ON ops_mission_proposals(priority_score DESC);
CREATE INDEX IF NOT EXISTS idx_proposals_created_at ON ops_mission_proposals(created_at DESC);

-- ============================================
-- 2. POLICY TABLE
-- Stores operational policies and constraints
-- ============================================
CREATE TABLE IF NOT EXISTS ops_policy (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Policy metadata
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    category VARCHAR(50) NOT NULL 
        CHECK (category IN ('cap_gates', 'scheduling', 'prioritization', 'execution', 'general')),
    
    -- Policy configuration
    config JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Policy rules (stored as JSON for flexibility)
    rules JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    effective_from TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    effective_until TIMESTAMP WITH TIME ZONE,
    
    -- Versioning
    version INTEGER NOT NULL DEFAULT 1,
    previous_version_id UUID REFERENCES ops_policy(id),
    
    -- Audit
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

-- Create indexes for policy
CREATE INDEX IF NOT EXISTS idx_policy_category ON ops_policy(category);
CREATE INDEX IF NOT EXISTS idx_policy_active ON ops_policy(is_active) WHERE is_active = true;

-- ============================================
-- 3. TRIGGER RULES TABLE
-- Stores automated trigger rules for the system
-- ============================================
CREATE TABLE IF NOT EXISTS ops_trigger_rules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Rule metadata
    name VARCHAR(100) NOT NULL,
    description TEXT,
    
    -- Trigger type
    trigger_type VARCHAR(50) NOT NULL 
        CHECK (trigger_type IN ('deadline_approaching', 'task_blocked', 'high_priority_created', 
                               'stale_task', 'sprint_ending', 'milestone_reached', 
                               'daily_mission_time', 'manual', 'calendar_conflict')),
    
    -- Trigger conditions (JSON for complex conditions)
    conditions JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Action to take when triggered
    action_type VARCHAR(50) NOT NULL 
        CHECK (action_type IN ('create_proposal', 'send_notification', 'escalate_priority', 
                               'auto_schedule', 'request_review', 'update_status')),
    
    action_config JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    -- Priority for rule evaluation order
    priority INTEGER NOT NULL DEFAULT 100,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    
    -- Rate limiting
    cooldown_minutes INTEGER DEFAULT 60,
    max_triggers_per_day INTEGER DEFAULT 10,
    
    -- Tracking
    last_triggered_at TIMESTAMP WITH TIME ZONE,
    trigger_count INTEGER DEFAULT 0,
    
    -- Audit
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

-- Create indexes for trigger_rules
CREATE INDEX IF NOT EXISTS idx_trigger_rules_type ON ops_trigger_rules(trigger_type);
CREATE INDEX IF NOT EXISTS idx_trigger_rules_active ON ops_trigger_rules(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_trigger_rules_priority ON ops_trigger_rules(priority);

-- ============================================
-- 4. CAP GATES TABLE
-- Stores queue management cap gates
-- ============================================
CREATE TABLE IF NOT EXISTS ops_cap_gates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Gate metadata
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    
    -- Gate type
    gate_type VARCHAR(50) NOT NULL 
        CHECK (gate_type IN ('daily_mission_limit', 'concurrent_high_priority', 
                           'workload_capacity', 'focus_time_minimum', 
                           'calendar_buffer', 'reaction_ratio')),
    
    -- Configuration
    max_value INTEGER NOT NULL,
    min_value INTEGER DEFAULT 0,
    unit VARCHAR(20) DEFAULT 'count',
    
    -- Current state
    current_value INTEGER DEFAULT 0,
    last_evaluated_at TIMESTAMP WITH TIME ZONE,
    
    -- Policy reference
    policy_id UUID REFERENCES ops_policy(id),
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT true,
    is_blocking BOOLEAN NOT NULL DEFAULT false,
    
    -- Evaluation window
    evaluation_window_hours INTEGER DEFAULT 24,
    
    -- Audit
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

-- Create indexes for cap_gates
CREATE INDEX IF NOT EXISTS idx_cap_gates_type ON ops_cap_gates(gate_type);
CREATE INDEX IF NOT EXISTS idx_cap_gates_active ON ops_cap_gates(is_active) WHERE is_active = true;

-- ============================================
-- 5. DAILY MISSIONS TABLE
-- Stores generated and scheduled daily missions
-- ============================================
CREATE TABLE IF NOT EXISTS ops_daily_missions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Mission date
    mission_date DATE NOT NULL,
    
    -- Generation metadata
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    generated_by VARCHAR(50) DEFAULT 'ai_agent' 
        CHECK (generated_by IN ('ai_agent', 'manual', 'trigger')),
    
    -- Mission status
    status VARCHAR(20) NOT NULL DEFAULT 'draft' 
        CHECK (status IN ('draft', 'review', 'approved', 'scheduled', 'in_progress', 'completed', 'canceled')),
    
    -- Proposal references (up to 3 missions per day)
    primary_mission_id UUID REFERENCES ops_mission_proposals(id),
    secondary_mission_id UUID REFERENCES ops_mission_proposals(id),
    tertiary_mission_id UUID REFERENCES ops_mission_proposals(id),
    
    -- Total estimated time
    total_estimated_minutes INTEGER,
    
    -- Focus time allocation
    focus_time_start TIMESTAMP WITH TIME ZONE,
    focus_time_end TIMESTAMP WITH TIME ZONE,
    focus_time_minutes INTEGER,
    
    -- Calendar blocks
    calendar_blocks JSONB DEFAULT '[]'::jsonb,
    
    -- Reaction matrix tracking
    planned_reactions INTEGER DEFAULT 0,
    actual_reactions INTEGER DEFAULT 0,
    reaction_ratio DECIMAL(3,2) DEFAULT 0.00,
    
    -- Completion tracking
    completed_at TIMESTAMP WITH TIME ZONE,
    completion_notes TEXT,
    completion_rating INTEGER CHECK (completion_rating >= 1 AND completion_rating <= 5),
    
    -- Feedback for AI learning
    user_feedback JSONB DEFAULT '{}'::jsonb,
    
    -- Unique constraint for one mission set per day
    CONSTRAINT unique_daily_mission UNIQUE (mission_date)
);

-- Create indexes for daily_missions
CREATE INDEX IF NOT EXISTS idx_daily_missions_date ON ops_daily_missions(mission_date);
CREATE INDEX IF NOT EXISTS idx_daily_missions_status ON ops_daily_missions(status);

-- ============================================
-- 6. CALENDAR BLOCKS TABLE
-- Stores scheduled focus time blocks
-- ============================================
CREATE TABLE IF NOT EXISTS ops_calendar_blocks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Block metadata
    block_date DATE NOT NULL,
    
    -- Time range
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Block type
    block_type VARCHAR(50) NOT NULL 
        CHECK (block_type IN ('deep_work', 'mission_execution', 'planning', 
                               'reaction_time', 'buffer', 'meeting')),
    
    -- Linked mission
    mission_id UUID REFERENCES ops_daily_missions(id),
    proposal_id UUID REFERENCES ops_mission_proposals(id),
    
    -- External calendar integration
    calendar_event_id TEXT,
    calendar_provider VARCHAR(20) DEFAULT 'google' 
        CHECK (calendar_provider IN ('google', 'outlook', 'apple', 'other')),
    calendar_synced BOOLEAN DEFAULT false,
    calendar_synced_at TIMESTAMP WITH TIME ZONE,
    
    -- Status
    status VARCHAR(20) DEFAULT 'scheduled' 
        CHECK (status IN ('scheduled', 'active', 'completed', 'canceled', 'rescheduled')),
    
    -- Notes
    notes TEXT,
    
    CONSTRAINT valid_block_times CHECK (end_time > start_time)
);

-- Create indexes for calendar_blocks
CREATE INDEX IF NOT EXISTS idx_calendar_blocks_date ON ops_calendar_blocks(block_date);
CREATE INDEX IF NOT EXISTS idx_calendar_blocks_mission ON ops_calendar_blocks(mission_id);
CREATE INDEX IF NOT EXISTS idx_calendar_blocks_status ON ops_calendar_blocks(status);

-- ============================================
-- 7. TRIGGER LOG TABLE
-- Stores trigger rule execution history
-- ============================================
CREATE TABLE IF NOT EXISTS ops_trigger_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Trigger reference
    rule_id UUID REFERENCES ops_trigger_rules(id),
    rule_name VARCHAR(100),
    
    -- Execution details
    triggered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    trigger_type VARCHAR(50) NOT NULL,
    
    -- Context
    context JSONB DEFAULT '{}'::jsonb,
    task_ids TEXT[] DEFAULT '{}',
    
    -- Result
    action_taken VARCHAR(50),
    action_result TEXT,
    success BOOLEAN DEFAULT true,
    error_message TEXT,
    
    -- Proposal created (if applicable)
    proposal_id UUID REFERENCES ops_mission_proposals(id)
);

-- Create indexes for trigger_log
CREATE INDEX IF NOT EXISTS idx_trigger_log_rule ON ops_trigger_log(rule_id);
CREATE INDEX IF NOT EXISTS idx_trigger_log_created ON ops_trigger_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_trigger_log_type ON ops_trigger_log(trigger_type);

-- ============================================
-- 8. AGENT EXECUTION LOG TABLE
-- Stores AI agent execution history
-- ============================================
CREATE TABLE IF NOT EXISTS ops_agent_execution_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Execution metadata
    agent_name VARCHAR(50) NOT NULL 
        CHECK (agent_name IN ('proposal_generator', 'cap_gate_validator', 'calendar_scheduler', 
                               'trigger_monitor', 'mission_optimizer', 'stale_task_detector')),
    
    execution_type VARCHAR(50) NOT NULL 
        CHECK (execution_type IN ('scheduled', 'triggered', 'manual', 'reaction')),
    
    -- Execution details
    started_at TIMESTAMP WITH TIME ZONE NOT NULL,
    completed_at TIMESTAMP WITH TIME ZONE,
    duration_ms INTEGER,
    
    -- Input/Output
    input_summary JSONB DEFAULT '{}'::jsonb,
    output_summary JSONB DEFAULT '{}'::jsonb,
    
    -- Results
    proposals_created INTEGER DEFAULT 0,
    proposals_approved INTEGER DEFAULT 0,
    tasks_analyzed INTEGER DEFAULT 0,
    
    -- Status
    status VARCHAR(20) DEFAULT 'running' 
        CHECK (status IN ('running', 'success', 'partial', 'failed')),
    error_message TEXT,
    
    -- Performance metrics
    ai_tokens_used INTEGER,
    ai_cost_usd DECIMAL(10,4)
);

-- Create indexes for agent_execution_log
CREATE INDEX IF NOT EXISTS idx_agent_log_agent ON ops_agent_execution_log(agent_name);
CREATE INDEX IF NOT EXISTS idx_agent_log_created ON ops_agent_execution_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_agent_log_status ON ops_agent_execution_log(status);

-- ============================================
-- DEFAULT POLICY INSERTS
-- ============================================

-- Insert default Cap Gates policies
INSERT INTO ops_policy (name, description, category, config, rules) VALUES
('daily_mission_limit', 'Maximum number of missions per day', 'cap_gates', 
 '{"max_missions_per_day": 3, "min_missions_per_day": 1}'::jsonb,
 '{"enforce": true, "allow_override": false}'::jsonb),

('concurrent_high_priority', 'Limit on concurrent high-priority work', 'cap_gates',
 '{"max_concurrent_high_priority": 2, "high_priority_threshold": 75}'::jsonb,
 '{"enforce": true, "escalation_required": true}'::jsonb),

('workload_capacity', 'Daily workload capacity in minutes', 'cap_gates',
 '{"max_daily_minutes": 480, "min_focus_block_minutes": 90}'::jsonb,
 '{"enforce": true, "buffer_percentage": 20}'::jsonb),

('focus_time_minimum', 'Minimum required focus time per day', 'cap_gates',
 '{"min_focus_minutes": 120, "preferred_focus_start": "09:00", "preferred_focus_end": "17:00"}'::jsonb,
 '{"enforce": true, "auto_protect": true}'::jsonb),

('calendar_buffer', 'Buffer time between calendar events', 'cap_gates',
 '{"min_buffer_minutes": 15, "default_buffer_minutes": 30}'::jsonb,
 '{"enforce": true, "auto_add_buffer": true}'::jsonb),

('reaction_ratio', 'Percentage of time reserved for spontaneous reactions', 'cap_gates',
 '{"reaction_ratio": 0.30, "min_reaction_minutes": 60}'::jsonb,
 '{"enforce": false, "track_only": true}'::jsonb);

-- Insert default Trigger Rules
INSERT INTO ops_trigger_rules (name, description, trigger_type, conditions, action_type, action_config, priority) VALUES
('deadline_approaching_24h', 'Trigger when deadline is within 24 hours', 'deadline_approaching',
 '{"hours_before": 24, "priority": "high"}'::jsonb, 'create_proposal',
 '{"priority_boost": 20, "rationale_template": "Deadline approaching in {{hours}} hours"}'::jsonb, 100),

('deadline_approaching_72h', 'Trigger when deadline is within 72 hours', 'deadline_approaching',
 '{"hours_before": 72, "priority": "medium"}'::jsonb, 'create_proposal',
 '{"priority_boost": 10, "rationale_template": "Deadline approaching in {{hours}} hours"}'::jsonb, 90),

('task_blocked_24h', 'Trigger when task has been blocked for 24+ hours', 'task_blocked',
 '{"blocked_hours": 24}'::jsonb, 'create_proposal',
 '{"action": "escalate_and_propose_alternative", "priority_boost": 30}'::jsonb, 95),

('high_priority_created', 'Trigger when high priority task is created', 'high_priority_created',
 '{"priority": "urgent"}'::jsonb, 'create_proposal',
 '{"immediate_review": true, "priority_boost": 25}'::jsonb, 110),

('stale_task_7d', 'Trigger when task is stale for 7+ days', 'stale_task',
 '{"stale_days": 7, "status_in": ["todo", "in-progress"]}'::jsonb, 'create_proposal',
 '{"action": "review_and_reprioritize", "priority_boost": 15}'::jsonb, 80),

('stale_task_14d', 'Trigger when task is stale for 14+ days', 'stale_task',
 '{"stale_days": 14, "status_in": ["todo", "in-progress"]}'::jsonb, 'create_proposal',
 '{"action": "flag_for_review", "priority_boost": 25}'::jsonb, 85),

('sprint_ending_3d', 'Trigger when sprint ends in 3 days', 'sprint_ending',
 '{"days_before": 3}'::jsonb, 'create_proposal',
 '{"focus": "sprint_completion", "priority_boost": 20}'::jsonb, 88),

('daily_mission_generation', 'Trigger daily mission generation at 7 AM', 'daily_mission_time',
 '{"hour": 7, "minute": 0}'::jsonb, 'create_proposal',
 '{"generate_count": 3, "include_reactions": true}'::jsonb, 120);

-- Insert default Cap Gates
INSERT INTO ops_cap_gates (name, description, gate_type, max_value, min_value, unit, policy_id) VALUES
('daily_mission_count', 'Maximum missions per day', 'daily_mission_limit', 3, 1, 'count', 
 (SELECT id FROM ops_policy WHERE name = 'daily_mission_limit')),

('concurrent_high_priority', 'Concurrent high priority items', 'concurrent_high_priority', 2, 0, 'count',
 (SELECT id FROM ops_policy WHERE name = 'concurrent_high_priority')),

('daily_workload', 'Daily workload capacity', 'workload_capacity', 480, 240, 'minutes',
 (SELECT id FROM ops_policy WHERE name = 'workload_capacity')),

('daily_focus_time', 'Minimum daily focus time', 'focus_time_minimum', 480, 120, 'minutes',
 (SELECT id FROM ops_policy WHERE name = 'focus_time_minimum')),

('reaction_time_reserve', 'Time reserved for reactions', 'reaction_ratio', 144, 60, 'minutes',
 (SELECT id FROM ops_policy WHERE name = 'reaction_ratio'));

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create updated_at triggers for all tables
CREATE TRIGGER update_ops_mission_proposals_updated_at BEFORE UPDATE ON ops_mission_proposals
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ops_policy_updated_at BEFORE UPDATE ON ops_policy
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ops_trigger_rules_updated_at BEFORE UPDATE ON ops_trigger_rules
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ops_cap_gates_updated_at BEFORE UPDATE ON ops_cap_gates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ops_daily_missions_updated_at BEFORE UPDATE ON ops_daily_missions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ops_calendar_blocks_updated_at BEFORE UPDATE ON ops_calendar_blocks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();