
-- Table Structure Documentation
-- Last updated: 2024

-- Sessions Table
-- Stores chat sessions information
CREATE TABLE sessions (
    id UUID PRIMARY KEY,                    -- Unique identifier for the session
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Session creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Last update timestamp
    thread_id TEXT,                         -- OpenAI thread identifier
    last_run_done BOOLEAN DEFAULT TRUE,     -- Indicates if last run was completed
    user_id UUID,                           -- Reference to users table
    company_id UUID                         -- Reference to companies table
);

-- Users Table
-- Stores user information
CREATE TABLE users (
    id UUID PRIMARY KEY,                    -- Unique identifier for the user
    name TEXT,                              -- User's full name
    email TEXT UNIQUE NOT NULL,             -- User's email address (unique)
    phone TEXT,                             -- User's phone number
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- User creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()   -- Last update timestamp
);

-- Messages Table
-- Stores chat messages
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,                  -- Auto-incrementing message ID
    session_id UUID NOT NULL,               -- Reference to sessions table
    role VARCHAR(50) NOT NULL,              -- Message role (user/assistant)
    content TEXT NOT NULL,                  -- Message content
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Message creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Last update timestamp
    CONSTRAINT valid_role CHECK (role IN ('user', 'assistant')),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Leads Table
-- Stores incorporation lead data
CREATE TABLE leads (
    session_id UUID PRIMARY KEY,            -- Reference to sessions table
    payload JSONB NOT NULL,                 -- Stores the IncorpLead type as JSON
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Lead creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Last update timestamp
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Members Table
-- Stores member information
CREATE TABLE members (
    id SERIAL PRIMARY KEY,                  -- Auto-incrementing member ID
    user_id UUID,                           -- Reference to users table
    group_id INTEGER,                       -- Group identifier
    address TEXT,                           -- Member's address
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Member creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Last update timestamp
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Company Members Table
-- Links companies with members
CREATE TABLE company_members (
    id SERIAL PRIMARY KEY,                  -- Auto-incrementing ID
    company_id INTEGER,                     -- Company identifier
    user_id UUID,                           -- Reference to users table
    address TEXT,                           -- Company member's address
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Record creation timestamp
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),  -- Last update timestamp
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Companies Table
-- Stores company information
CREATE TABLE companies (
    id UUID PRIMARY KEY,                    -- Company unique identifier
    user_id UUID,                           -- Reference to users table
    name TEXT NOT NULL,                     -- Company name
    state TEXT,                             -- State of incorporation
    entity_type TEXT,                       -- Type of business entity
    purpose TEXT,                           -- Business purpose
    naics_code TEXT,                        -- NAICS industry code
    session_id UUID,                        -- Reference to sessions table
    designator TEXT,                        -- Company name designator
    created_at TIMESTAMP DEFAULT NOW(),     -- Company creation timestamp
    updated_at TIMESTAMP DEFAULT NOW(),     -- Last update timestamp
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes for Performance
CREATE INDEX idx_messages_session_id ON messages(session_id);
CREATE INDEX idx_leads_updated_at ON leads(updated_at);
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX companies_session_name_idx ON companies(session_id, name);

-- Notes:
-- 1. All tables include created_at and updated_at timestamps
-- 2. UUID is used for primary keys in core tables (sessions, users, companies)
-- 3. SERIAL is used for auto-incrementing IDs in secondary tables
-- 4. Foreign key constraints ensure referential integrity
-- 5. Cascade deletion is implemented where appropriate
-- 6. Address fields added to members and company_members tables
