-- Drop existing tables if they exist
DROP TABLE IF EXISTS leads CASCADE;
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS sessions CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS members CASCADE;
DROP TABLE IF EXISTS company_members CASCADE;

-- Sessions table - stores chat sessions
CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Leads table - stores incorporation lead data
CREATE TABLE leads (
    session_id UUID PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
    payload JSONB NOT NULL, -- Stores the IncorpLead type as JSON
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Messages table - stores chat history
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL, -- 'user' or 'assistant'
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP NOT NULL DEFAULT now(),
    CONSTRAINT valid_role CHECK (role IN ('user', 'assistant'))
);

-- Users table - stores user information
CREATE TABLE users (
    id UUID PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Members table - example structure
CREATE TABLE members (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    group_id INTEGER,
    address TEXT,
    ownership_pct NUMERIC,
    role TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE(user_id, group_id)
);


-- Company Members table - example structure
CREATE TABLE company_members (
    id SERIAL PRIMARY KEY,
    company_id UUID,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    address TEXT,
    ownership_pct NUMERIC,
    role TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE(company_id, user_id)
);

-- Create indexes for better query 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 TABLE companies (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    state TEXT,
    entity_type TEXT,
    purpose TEXT,
    naics_code TEXT,
    session_id UUID,
    updated_at TIMESTAMP DEFAULT NOW()
);