-- Database Connector Plugin - Migration 001
-- Creates all necessary tables for the plugin

-- 1. Database Connections
CREATE TABLE IF NOT EXISTS db_connections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    
    -- Connection Info
    name VARCHAR(255) NOT NULL,
    description TEXT,
    connector_type VARCHAR(50) NOT NULL, -- mysql, postgres, mongodb, rest_api, graphql
    
    -- Connection Config (encrypted JSON)
    connection_config TEXT NOT NULL, -- Encrypted: {host, port, database, username, password, ssl, etc.}
    
    -- Security Settings
    allowed_tables TEXT[], -- Tables allowed to access (null = all)
    allowed_operations TEXT[] DEFAULT ARRAY['read'], -- read, write, delete
    row_level_security JSONB, -- RLS rules: {table: {column: "user_id", value: "{{caller_id}}"}}
    
    -- Connection Status
    is_active BOOLEAN DEFAULT true,
    last_health_check TIMESTAMP,
    health_status VARCHAR(20) DEFAULT 'unknown', -- healthy, unhealthy, unknown
    health_message TEXT,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 2. Discovered Schemas (Cache)
CREATE TABLE IF NOT EXISTS db_discovered_schemas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id UUID NOT NULL REFERENCES db_connections(id) ON DELETE CASCADE,
    
    table_name VARCHAR(255) NOT NULL,
    table_description TEXT, -- Manual description for AI context
    columns JSONB NOT NULL, -- [{name, type, nullable, is_primary, is_foreign, description}]
    primary_key TEXT[],
    foreign_keys JSONB, -- [{column, references_table, references_column}]
    
    -- AI Context
    semantic_description TEXT, -- Semantic description for Text-to-SQL
    sample_queries TEXT[], -- Example queries for this table
    
    -- Stats
    row_count_estimate INTEGER,
    
    discovered_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    UNIQUE(connection_id, table_name)
);

-- 3. Saved Queries
CREATE TABLE IF NOT EXISTS db_saved_queries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id UUID NOT NULL REFERENCES db_connections(id) ON DELETE CASCADE,
    user_id VARCHAR(255) NOT NULL,
    
    name VARCHAR(255) NOT NULL,
    description TEXT,
    
    -- Query Definition
    query_template TEXT NOT NULL, -- SQL with {{parameters}}
    parameters JSONB, -- [{name, type, required, default, description}]
    
    -- AI Agent Integration
    natural_language_triggers TEXT[], -- ["ما رصيدي", "show my balance", "كم المبلغ"]
    response_template TEXT, -- "رصيدك {{balance}} ريال"
    
    -- Settings
    is_system BOOLEAN DEFAULT false,
    cache_ttl_seconds INTEGER DEFAULT 0, -- 0 = no cache
    max_rows INTEGER DEFAULT 100,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 4. Saved Actions (INSERT/UPDATE/DELETE)
CREATE TABLE IF NOT EXISTS db_saved_actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id UUID NOT NULL REFERENCES db_connections(id) ON DELETE CASCADE,
    user_id VARCHAR(255) NOT NULL,
    
    name VARCHAR(255) NOT NULL,
    description TEXT,
    action_type VARCHAR(50) NOT NULL, -- insert, update, delete, api_call
    
    -- Action Definition
    action_template JSONB NOT NULL, -- SQL template or API config
    parameters JSONB, -- Required parameters
    validation_rules JSONB, -- Validation for each parameter
    
    -- AI Agent Integration
    natural_language_triggers TEXT[],
    confirmation_required BOOLEAN DEFAULT true,
    confirmation_message TEXT, -- "هل تريد تأكيد إلغاء الطلب رقم {{order_id}}?"
    success_message TEXT, -- "تم إلغاء الطلب بنجاح"
    failure_message TEXT,
    
    -- Safety
    requires_approval BOOLEAN DEFAULT false, -- Admin approval required
    max_affected_rows INTEGER DEFAULT 1,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 5. Document Templates
CREATE TABLE IF NOT EXISTS db_document_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    connection_id UUID REFERENCES db_connections(id) ON DELETE SET NULL,
    
    name VARCHAR(255) NOT NULL,
    description TEXT,
    template_type VARCHAR(50) NOT NULL, -- invoice, receipt, statement, report, custom
    
    -- Template Content
    html_template TEXT NOT NULL, -- HTML with Handlebars placeholders
    css_styles TEXT,
    header_template TEXT,
    footer_template TEXT,
    
    -- Data Source
    data_query TEXT, -- SQL to fetch data for the template
    parameters JSONB,
    
    -- PDF Settings
    paper_size VARCHAR(20) DEFAULT 'A4', -- A4, Letter, Legal
    orientation VARCHAR(20) DEFAULT 'portrait', -- portrait, landscape
    margins JSONB DEFAULT '{"top": 20, "right": 20, "bottom": 20, "left": 20}',
    
    -- Delivery Options
    default_delivery_method VARCHAR(50) DEFAULT 'email', -- email, whatsapp, sms, download
    email_subject_template TEXT,
    email_body_template TEXT,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 6. Generated Documents
CREATE TABLE IF NOT EXISTS db_generated_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id UUID REFERENCES db_document_templates(id) ON DELETE SET NULL,
    user_id VARCHAR(255) NOT NULL,
    
    document_number VARCHAR(100),
    document_type VARCHAR(50),
    
    -- File
    file_url TEXT NOT NULL,
    file_name VARCHAR(255),
    file_size INTEGER,
    
    -- Data Used
    data_snapshot JSONB, -- Data at generation time
    
    -- Delivery
    delivered_via VARCHAR(50), -- email, whatsapp, sms, download
    delivered_to TEXT,
    delivered_at TIMESTAMP,
    delivery_status VARCHAR(50) DEFAULT 'pending', -- pending, sent, failed
    
    -- Context
    agent_id UUID,
    call_id UUID,
    
    created_at TIMESTAMP DEFAULT NOW()
);

-- 7. Scheduled Callbacks (for debt collection, follow-ups, etc.)
CREATE TABLE IF NOT EXISTS db_scheduled_callbacks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    agent_id UUID NOT NULL,
    
    -- Target
    phone_number VARCHAR(50) NOT NULL,
    customer_name VARCHAR(255),
    customer_id VARCHAR(255), -- ID in external system
    
    -- Schedule
    scheduled_at TIMESTAMP NOT NULL,
    timezone VARCHAR(50) DEFAULT 'Asia/Riyadh',
    
    -- Callback Reason
    reason VARCHAR(255) NOT NULL, -- payment_reminder, follow_up, appointment_confirmation
    reason_details JSONB, -- {amount: 500, invoice_id: "INV-001", etc.}
    
    -- Script/Context for Agent
    agent_context TEXT, -- Context to inject into agent prompt
    data_query_id UUID REFERENCES db_saved_queries(id), -- Query to run before call
    
    -- Status
    status VARCHAR(50) DEFAULT 'pending', -- pending, in_progress, completed, failed, cancelled
    
    -- Retry Logic
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    retry_interval_minutes INTEGER DEFAULT 60, -- Wait time between retries
    last_attempt_at TIMESTAMP,
    next_retry_at TIMESTAMP,
    
    -- Result
    call_id UUID, -- AgentLabs call ID when executed
    call_result VARCHAR(50), -- answered, no_answer, busy, voicemail, failed
    call_summary TEXT, -- AI summary of the call
    follow_up_scheduled BOOLEAN DEFAULT false,
    
    -- Origin
    created_by VARCHAR(50) DEFAULT 'manual', -- manual, agent, api, campaign
    original_call_id UUID, -- If created from another call
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 8. Callback Execution Logs
CREATE TABLE IF NOT EXISTS db_callback_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    callback_id UUID NOT NULL REFERENCES db_scheduled_callbacks(id) ON DELETE CASCADE,
    
    attempt_number INTEGER NOT NULL,
    attempted_at TIMESTAMP DEFAULT NOW(),
    
    -- Result
    status VARCHAR(50) NOT NULL, -- initiated, answered, no_answer, busy, failed
    call_id UUID, -- AgentLabs call ID
    duration_seconds INTEGER,
    
    -- Details
    error_message TEXT,
    call_summary TEXT,
    
    -- Next Action
    next_action VARCHAR(50), -- retry, schedule_new, complete, escalate
    next_action_details JSONB
);

-- 9. Agent-Connection Mapping
CREATE TABLE IF NOT EXISTS db_agent_connections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id UUID NOT NULL,
    connection_id UUID NOT NULL REFERENCES db_connections(id) ON DELETE CASCADE,
    
    -- Permissions for this agent
    allowed_queries UUID[], -- Allowed saved query IDs
    allowed_actions UUID[], -- Allowed saved action IDs
    allowed_documents UUID[], -- Allowed document template IDs
    can_schedule_callbacks BOOLEAN DEFAULT false,
    
    -- Settings
    is_active BOOLEAN DEFAULT true,
    
    -- Context injection
    context_query_id UUID REFERENCES db_saved_queries(id), -- Run before each call
    
    created_at TIMESTAMP DEFAULT NOW(),
    
    UNIQUE(agent_id, connection_id)
);

-- 10. Usage Logs
CREATE TABLE IF NOT EXISTS db_usage_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    connection_id UUID REFERENCES db_connections(id) ON DELETE SET NULL,
    user_id VARCHAR(255) NOT NULL,
    agent_id UUID,
    call_id UUID,
    
    -- Operation
    operation_type VARCHAR(50) NOT NULL, -- query, action, document, callback
    operation_id UUID, -- query_id, action_id, template_id, or callback_id
    operation_name VARCHAR(255),
    
    -- Details
    input_parameters JSONB,
    
    -- Result
    success BOOLEAN,
    rows_affected INTEGER,
    execution_time_ms INTEGER,
    error_message TEXT,
    
    -- Audit
    ip_address VARCHAR(50),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_db_connections_user ON db_connections(user_id);
CREATE INDEX IF NOT EXISTS idx_db_connections_active ON db_connections(is_active) WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_db_schemas_connection ON db_discovered_schemas(connection_id);

CREATE INDEX IF NOT EXISTS idx_db_queries_connection ON db_saved_queries(connection_id);
CREATE INDEX IF NOT EXISTS idx_db_queries_user ON db_saved_queries(user_id);

CREATE INDEX IF NOT EXISTS idx_db_actions_connection ON db_saved_actions(connection_id);
CREATE INDEX IF NOT EXISTS idx_db_actions_user ON db_saved_actions(user_id);

CREATE INDEX IF NOT EXISTS idx_db_documents_user ON db_generated_documents(user_id);
CREATE INDEX IF NOT EXISTS idx_db_documents_created ON db_generated_documents(created_at);

CREATE INDEX IF NOT EXISTS idx_db_callbacks_user ON db_scheduled_callbacks(user_id);
CREATE INDEX IF NOT EXISTS idx_db_callbacks_scheduled ON db_scheduled_callbacks(scheduled_at);
CREATE INDEX IF NOT EXISTS idx_db_callbacks_status ON db_scheduled_callbacks(status);
CREATE INDEX IF NOT EXISTS idx_db_callbacks_pending ON db_scheduled_callbacks(status, scheduled_at) 
    WHERE status IN ('pending', 'in_progress');

CREATE INDEX IF NOT EXISTS idx_db_agent_connections_agent ON db_agent_connections(agent_id);
CREATE INDEX IF NOT EXISTS idx_db_agent_connections_connection ON db_agent_connections(connection_id);

CREATE INDEX IF NOT EXISTS idx_db_usage_logs_connection ON db_usage_logs(connection_id);
CREATE INDEX IF NOT EXISTS idx_db_usage_logs_user ON db_usage_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_db_usage_logs_created ON db_usage_logs(created_at);

-- 11. Webhook Configurations
CREATE TABLE IF NOT EXISTS db_webhooks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    
    name VARCHAR(255) NOT NULL,
    url TEXT NOT NULL,
    secret VARCHAR(255) NOT NULL, -- For signing payloads
    
    -- Events to send
    events TEXT[] NOT NULL, -- callback.scheduled, callback.completed, payment.promised, etc.
    
    -- Settings
    is_active BOOLEAN DEFAULT true,
    retry_count INTEGER DEFAULT 3,
    retry_delay_seconds INTEGER DEFAULT 60,
    
    -- Headers
    custom_headers JSONB,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 12. Webhook Delivery Logs
CREATE TABLE IF NOT EXISTS db_webhook_deliveries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    webhook_id UUID NOT NULL REFERENCES db_webhooks(id) ON DELETE CASCADE,
    
    event_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    
    -- Request
    request_body TEXT,
    
    -- Response
    response_status INTEGER,
    response_body TEXT,
    
    -- Status
    success BOOLEAN,
    attempt_number INTEGER DEFAULT 1,
    error_message TEXT,
    
    created_at TIMESTAMP DEFAULT NOW()
);

-- 13. Call Scripts
CREATE TABLE IF NOT EXISTS db_call_scripts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NOT NULL, -- payment_reminder_friendly, payment_reminder_firm, etc.
    language VARCHAR(10) DEFAULT 'ar',
    
    -- Script Content
    greeting TEXT NOT NULL,
    main_content TEXT NOT NULL,
    closing_positive TEXT,
    closing_negative TEXT,
    
    -- Objection Handlers
    objection_handlers JSONB, -- [{objection, keywords, response, nextAction}]
    
    -- Settings
    tone VARCHAR(50) DEFAULT 'professional', -- friendly, professional, firm, urgent
    escalation_level INTEGER DEFAULT 1, -- 1-5
    
    -- Variables
    variables TEXT[], -- customerName, amount, dueDate, etc.
    
    is_default BOOLEAN DEFAULT false,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 14. Customer Scores (Cached)
CREATE TABLE IF NOT EXISTS db_customer_scores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    customer_id VARCHAR(255) NOT NULL,
    
    -- Scores
    payment_score INTEGER, -- 0-100
    response_score INTEGER, -- 0-100
    urgency_score INTEGER, -- 0-100
    overall_score INTEGER, -- 0-100
    
    -- Insights
    best_contact_time VARCHAR(50),
    best_contact_day VARCHAR(50),
    preferred_channel VARCHAR(50), -- call, sms, whatsapp, email
    risk_level VARCHAR(20), -- low, medium, high, critical
    
    -- Financial
    total_due DECIMAL(12, 2),
    days_overdue INTEGER,
    
    -- Recommendations
    recommended_action TEXT,
    recommended_script_id UUID REFERENCES db_call_scripts(id),
    escalation_needed BOOLEAN DEFAULT false,
    
    calculated_at TIMESTAMP DEFAULT NOW(),
    
    UNIQUE(user_id, customer_id)
);

-- 15. Payment Promises
CREATE TABLE IF NOT EXISTS db_payment_promises (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL,
    customer_id VARCHAR(255) NOT NULL,
    
    -- Promise Details
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'SAR',
    promised_date DATE NOT NULL,
    
    -- Context
    call_id UUID,
    callback_id UUID REFERENCES db_scheduled_callbacks(id),
    
    -- Status
    status VARCHAR(50) DEFAULT 'pending', -- pending, kept, broken, partial
    actual_payment_date DATE,
    actual_amount DECIMAL(12, 2),
    
    -- Follow-up
    follow_up_scheduled BOOLEAN DEFAULT false,
    follow_up_callback_id UUID REFERENCES db_scheduled_callbacks(id),
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Additional Indexes
CREATE INDEX IF NOT EXISTS idx_db_webhooks_user ON db_webhooks(user_id);
CREATE INDEX IF NOT EXISTS idx_db_webhooks_active ON db_webhooks(is_active) WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_db_webhook_deliveries_webhook ON db_webhook_deliveries(webhook_id);
CREATE INDEX IF NOT EXISTS idx_db_webhook_deliveries_created ON db_webhook_deliveries(created_at);

CREATE INDEX IF NOT EXISTS idx_db_scripts_user ON db_call_scripts(user_id);
CREATE INDEX IF NOT EXISTS idx_db_scripts_category ON db_call_scripts(category);

CREATE INDEX IF NOT EXISTS idx_db_customer_scores_user ON db_customer_scores(user_id);
CREATE INDEX IF NOT EXISTS idx_db_customer_scores_customer ON db_customer_scores(customer_id);
CREATE INDEX IF NOT EXISTS idx_db_customer_scores_overall ON db_customer_scores(overall_score DESC);

CREATE INDEX IF NOT EXISTS idx_db_payment_promises_user ON db_payment_promises(user_id);
CREATE INDEX IF NOT EXISTS idx_db_payment_promises_customer ON db_payment_promises(customer_id);
CREATE INDEX IF NOT EXISTS idx_db_payment_promises_status ON db_payment_promises(status);
CREATE INDEX IF NOT EXISTS idx_db_payment_promises_date ON db_payment_promises(promised_date);