CREATE TABLE IF NOT EXISTS migrations (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, password_hash TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS businesses (id TEXT PRIMARY KEY, owner_id TEXT NOT NULL REFERENCES users(id), name TEXT NOT NULL, website TEXT NOT NULL DEFAULT '', industry TEXT NOT NULL DEFAULT 'Home services', city TEXT NOT NULL DEFAULT '', area TEXT NOT NULL DEFAULT '', timezone TEXT NOT NULL DEFAULT 'America/New_York', contact_email TEXT NOT NULL DEFAULT '', phone TEXT NOT NULL DEFAULT '', services TEXT NOT NULL DEFAULT '[]', hours TEXT NOT NULL DEFAULT '', faqs TEXT NOT NULL DEFAULT '[]', policies TEXT NOT NULL DEFAULT '', questions TEXT NOT NULL DEFAULT '[]', criteria TEXT NOT NULL DEFAULT '', greeting TEXT NOT NULL DEFAULT '', tone TEXT NOT NULL DEFAULT 'Warm and concise', booking_url TEXT NOT NULL DEFAULT '', handoff_mode TEXT NOT NULL DEFAULT 'email', allowed_origins TEXT NOT NULL DEFAULT '', retention_days INTEGER NOT NULL DEFAULT 180, plan TEXT NOT NULL DEFAULT 'starter', stripe_subscription_id TEXT NOT NULL DEFAULT '', stripe_customer_id TEXT NOT NULL DEFAULT '', subscription_status TEXT NOT NULL DEFAULT 'local', require_area_match INTEGER NOT NULL DEFAULT 1, require_service_match INTEGER NOT NULL DEFAULT 1, published INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX IF NOT EXISTS businesses_owner ON businesses(owner_id);
CREATE TABLE IF NOT EXISTS sessions (id_hash TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id), csrf TEXT NOT NULL, expires_at INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS reset_tokens (token_hash TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id), expires_at INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS conversations (id TEXT PRIMARY KEY, business_id TEXT NOT NULL REFERENCES businesses(id), visitor_token_hash TEXT NOT NULL, source_page TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'open', service TEXT NOT NULL DEFAULT '', location TEXT NOT NULL DEFAULT '', urgency TEXT NOT NULL DEFAULT '', details TEXT NOT NULL DEFAULT '', needs_attention INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX IF NOT EXISTS conversations_business ON conversations(business_id, created_at);
CREATE TABLE IF NOT EXISTS messages (id TEXT PRIMARY KEY, conversation_id TEXT NOT NULL REFERENCES conversations(id), role TEXT NOT NULL, body TEXT NOT NULL, evidence TEXT NOT NULL DEFAULT '[]', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX IF NOT EXISTS messages_conversation ON messages(conversation_id, created_at);
CREATE TABLE IF NOT EXISTS leads (id TEXT PRIMARY KEY, business_id TEXT NOT NULL REFERENCES businesses(id), conversation_id TEXT NOT NULL UNIQUE REFERENCES conversations(id), status TEXT NOT NULL DEFAULT 'New', qualified INTEGER NOT NULL DEFAULT 0, qualification_reason TEXT NOT NULL DEFAULT '', service TEXT NOT NULL DEFAULT '', location TEXT NOT NULL DEFAULT '', urgency TEXT NOT NULL DEFAULT '', details TEXT NOT NULL DEFAULT '', name TEXT NOT NULL DEFAULT '', email TEXT NOT NULL DEFAULT '', phone TEXT NOT NULL DEFAULT '', contact_method TEXT NOT NULL DEFAULT '', consent_at TEXT, summary TEXT NOT NULL DEFAULT '', notes TEXT NOT NULL DEFAULT '', assignee TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX IF NOT EXISTS leads_business ON leads(business_id, created_at);
CREATE TABLE IF NOT EXISTS notifications (id TEXT PRIMARY KEY, business_id TEXT NOT NULL, lead_id TEXT NOT NULL, kind TEXT NOT NULL, destination TEXT NOT NULL, state TEXT NOT NULL, error TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS usage (business_id TEXT NOT NULL, period TEXT NOT NULL, conversations INTEGER NOT NULL DEFAULT 0, ai_input_tokens INTEGER NOT NULL DEFAULT 0, ai_output_tokens INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(business_id, period));
CREATE TABLE IF NOT EXISTS audit_log (id TEXT PRIMARY KEY, business_id TEXT NOT NULL, actor_id TEXT NOT NULL, action TEXT NOT NULL, detail TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS stripe_events (id TEXT PRIMARY KEY, received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);