-- Migration 056: Audit AI Actions, Cache, and Social Connections
-- Created: 2026-02-07
-- Purpose: Tables for AI-powered audit analysis, action tracking, and social media OAuth connections

BEGIN;

-- ============================================================
-- AUDIT ACTIONS - AI-generated action items per audit type
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_actions (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    audit_type VARCHAR(20) NOT NULL,        -- 'seo', 'gbp', 'social'
    action_type VARCHAR(50) NOT NULL,       -- 'generate_schema_org', 'generate_gbp_post', etc.
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority VARCHAR(20) DEFAULT 'medium',  -- 'critical', 'high', 'medium', 'low'
    impact_score INT,                       -- 1-10
    effort_score INT,                       -- 1-10
    ai_content TEXT,                        -- Generated content (JSON for complex structures)
    ai_model VARCHAR(50),                   -- Model used for generation
    status VARCHAR(20) DEFAULT 'suggested', -- 'suggested', 'approved', 'implemented', 'dismissed'
    platform VARCHAR(30),                   -- 'google', 'facebook', 'instagram', 'linkedin', 'website'
    created_by INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    implemented_at TIMESTAMP
);

CREATE INDEX idx_audit_actions_company ON audit_actions(company_id, audit_type);
CREATE INDEX idx_audit_actions_status ON audit_actions(status);

-- ============================================================
-- AUDIT AI CACHE - Cached AI analyses to avoid regeneration
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_ai_cache (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    audit_type VARCHAR(20) NOT NULL,
    analysis_summary TEXT,                  -- AI-generated summary paragraph
    actions_json JSONB,                     -- Cached action list
    audit_data_hash VARCHAR(64),            -- SHA256 of input data (invalidate when data changes)
    generated_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP,                   -- Auto-expire after 7 days
    UNIQUE(company_id, audit_type)
);

-- ============================================================
-- SOCIAL CONNECTIONS - OAuth tokens for publishing (Phase 2-3)
-- ============================================================
CREATE TABLE IF NOT EXISTS social_connections (
    id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    platform VARCHAR(30) NOT NULL,          -- 'google_business', 'facebook', 'instagram', 'linkedin'
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP,
    scope TEXT,
    external_account_id VARCHAR(255),
    external_account_name VARCHAR(255),
    connected_by INT REFERENCES users(id),
    connected_at TIMESTAMP DEFAULT NOW(),
    is_active BOOLEAN DEFAULT TRUE,
    UNIQUE(company_id, platform)
);

-- ============================================================
-- GRANTS
-- ============================================================
GRANT ALL ON TABLE audit_actions TO nordabiz_app;
GRANT ALL ON TABLE audit_ai_cache TO nordabiz_app;
GRANT ALL ON TABLE social_connections TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE audit_actions_id_seq TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE audit_ai_cache_id_seq TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE social_connections_id_seq TO nordabiz_app;

COMMIT;