supabase_schema.sql

-- ProspectIQ Supabase PostgreSQL Database Schema

-- Enable pgvector if not already enabled (for future vector functionality)
CREATE EXTENSION IF NOT EXISTS vector;

-- Drop tables if they exist (for a clean setup, but we'll use IF NOT EXISTS)
-- 1. CRM Schema Tables
CREATE TABLE IF NOT EXISTS tenants (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    brand_color VARCHAR(50),
    logo_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS permissions (
    id SERIAL PRIMARY KEY,
    code VARCHAR(255) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS roles (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(tenant_id, name)
);

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

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    password_hash TEXT,
    role_id INTEGER REFERENCES roles(id),
    role VARCHAR(50) NOT NULL CHECK(role IN ('Super Admin', 'Admin', 'BD Rep', 'Viewer')),
    is_active INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stages (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    order_index INTEGER NOT NULL UNIQUE,
    color_bg VARCHAR(50),
    color_text VARCHAR(50),
    is_active INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS import_batches (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    filename VARCHAR(255),
    status VARCHAR(50),
    total_rows INTEGER,
    added INTEGER,
    updated INTEGER,
    skipped INTEGER,
    errored INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS companies (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255),
    website TEXT,
    website_link TEXT,
    phone VARCHAR(50),
    nic_code VARCHAR(50),
    industry VARCHAR(255),
    street TEXT,
    city VARCHAR(255),
    province VARCHAR(255),
    postal_code VARCHAR(50),
    country VARCHAR(255),
    size_staff INTEGER,
    revenue_band VARCHAR(255),
    business_type VARCHAR(50) CHECK(business_type IN ('OEM', 'distributor', 'other', 'OEM / distributor', 'OEM / Distributor')),
    products_made TEXT,
    parts_sourced TEXT,
    current_supplier_notes TEXT,
    maplempss_fit_score REAL DEFAULT 0.0,
    stage_id INTEGER NOT NULL REFERENCES stages(id),
    assigned_rep_id INTEGER REFERENCES users(id),
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id),
    import_batch_id INTEGER, -- Not enforced by constraint to prevent cyclic dependencies easily
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS contacts (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    title VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    linkedin TEXT,
    is_primary INTEGER DEFAULT 0 CHECK(is_primary IN (0, 1)),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS activities (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    type VARCHAR(50) NOT NULL CHECK(type IN ('Cold Call', 'Email', 'LinkedIn Message', 'Meeting', 'Follow-Up', 'Note')),
    outcome VARCHAR(255),
    notes TEXT,
    next_action TEXT,
    next_action_date VARCHAR(50),
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS notes (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    contact_id INTEGER REFERENCES contacts(id) ON DELETE SET NULL,
    user_id INTEGER NOT NULL REFERENCES users(id),
    body TEXT NOT NULL,
    is_pinned INTEGER DEFAULT 0 CHECK(is_pinned IN (0, 1)),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stage_changes (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    from_stage_id INTEGER REFERENCES stages(id),
    to_stage_id INTEGER NOT NULL REFERENCES stages(id),
    changed_by_user_id INTEGER NOT NULL REFERENCES users(id),
    source VARCHAR(50) NOT NULL CHECK(source IN ('manual', 'ai_suggested', 'ai_confirmed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ai_field_values (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    field_name VARCHAR(255) NOT NULL,
    value TEXT,
    source_url TEXT,
    confidence_score REAL,
    enriched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    confirmed_by_user_id INTEGER REFERENCES users(id),
    confirmed_at TIMESTAMP
);

-- Indices for fast searching and filtering
CREATE INDEX IF NOT EXISTS idx_companies_stage ON companies(stage_id);
CREATE INDEX IF NOT EXISTS idx_companies_tenant ON companies(tenant_id);
CREATE INDEX IF NOT EXISTS idx_companies_rep ON companies(assigned_rep_id);
CREATE INDEX IF NOT EXISTS idx_contacts_company ON contacts(company_id);
CREATE INDEX IF NOT EXISTS idx_activities_company ON activities(company_id);
CREATE INDEX IF NOT EXISTS idx_notes_company ON notes(company_id);

-- 2. AI / Agent Interaction History Table
CREATE TABLE IF NOT EXISTS interactions (
    id VARCHAR(255) PRIMARY KEY,
    kind VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL,
    input_json TEXT NOT NULL DEFAULT '{}',
    output_json TEXT NOT NULL DEFAULT '{}',
    result_text TEXT NOT NULL DEFAULT '',
    logs_json TEXT NOT NULL DEFAULT '[]',
    error TEXT NOT NULL DEFAULT '',
    run_dir TEXT NOT NULL DEFAULT '',
    profile_path TEXT NOT NULL DEFAULT '',
    command_json TEXT NOT NULL DEFAULT '[]',
    returncode INTEGER,
    pid INTEGER,
    created_at VARCHAR(50) NOT NULL,
    started_at VARCHAR(50) NOT NULL DEFAULT '',
    finished_at VARCHAR(50) NOT NULL DEFAULT '',
    updated_at VARCHAR(50) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_interactions_created_at ON interactions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_interactions_kind ON interactions(kind);
CREATE INDEX IF NOT EXISTS idx_interactions_kind_created ON interactions(kind, created_at DESC);

-- 3. Restructured RAG Playbook Tables
CREATE TABLE IF NOT EXISTS rag_collections (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    user_id VARCHAR(255),
    visibility VARCHAR(50) DEFAULT 'private',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rag_documents (
    id VARCHAR(255) PRIMARY KEY,
    collection_id VARCHAR(255) REFERENCES rag_collections(id) ON DELETE CASCADE,
    filename VARCHAR(255) NOT NULL,
    original_type VARCHAR(50),
    pages INTEGER DEFAULT 0,
    chunks INTEGER DEFAULT 0,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    upload_path TEXT
);

CREATE TABLE IF NOT EXISTS rag_sessions (
    id VARCHAR(255) PRIMARY KEY,
    collection_id VARCHAR(255) REFERENCES rag_collections(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rag_messages (
    id SERIAL PRIMARY KEY,
    collection_id VARCHAR(255) REFERENCES rag_collections(id) ON DELETE CASCADE,
    session_id VARCHAR(255) REFERENCES rag_sessions(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    sources JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);