-- Migration 005: Estimate-to-Close Funnel + Jobber Connector models
-- P2: remodeling sector gap plan

-- =============================================================================
-- Estimate-to-Close Funnel
-- =============================================================================

CREATE TABLE IF NOT EXISTS estimates (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    source VARCHAR(50) DEFAULT 'manual' CHECK (source IN ('servicetitan', 'jobber', 'hubspot', 'angi', 'manual')),
    external_id VARCHAR(128),
    connector_id VARCHAR(36),
    prospect_name VARCHAR(255) DEFAULT '',
    prospect_email VARCHAR(255) DEFAULT '',
    prospect_phone VARCHAR(50) DEFAULT '',
    address VARCHAR(500) DEFAULT '',
    city VARCHAR(100) DEFAULT '',
    state VARCHAR(50) DEFAULT '',
    zip_code VARCHAR(20) DEFAULT '',
    project_type VARCHAR(100) DEFAULT '',
    description TEXT DEFAULT '',
    estimate_amount FLOAT,
    estimated_cost FLOAT,
    estimated_margin FLOAT,
    stage VARCHAR(50) DEFAULT 'lead_received' CHECK (stage IN ('lead_received', 'contacted', 'estimate_delivered', 'estimate_accepted', 'estimate_rejected', 'won', 'lost', 'expired')),
    lost_reason VARCHAR(200) DEFAULT '',
    lead_received_at DATETIME,
    contacted_at DATETIME,
    estimate_delivered_at DATETIME,
    estimate_accepted_at DATETIME,
    estimate_rejected_at DATETIME,
    closed_at DATETIME,
    angi_lead_id VARCHAR(36) REFERENCES angi_leads(id) ON DELETE SET NULL,
    crm_deal_id VARCHAR(36) REFERENCES crm_deals(id) ON DELETE SET NULL,
    internal_notes TEXT DEFAULT '',
    created_at DATETIME,
    updated_at DATETIME
);

CREATE INDEX IF NOT EXISTS idx_estimates_company_id ON estimates(company_id);
CREATE INDEX IF NOT EXISTS idx_estimates_external_id ON estimates(external_id);
CREATE INDEX IF NOT EXISTS idx_estimates_connector_id ON estimates(connector_id);
CREATE INDEX IF NOT EXISTS idx_estimates_stage ON estimates(stage);

-- =============================================================================
-- Jobber connector models
-- =============================================================================

CREATE TABLE IF NOT EXISTS jobber_clients (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    external_id VARCHAR(128) NOT NULL,
    first_name VARCHAR(255) DEFAULT '',
    last_name VARCHAR(255) DEFAULT '',
    email VARCHAR(255) DEFAULT '',
    phone VARCHAR(50) DEFAULT '',
    company_name VARCHAR(255) DEFAULT '',
    address_line1 VARCHAR(500) DEFAULT '',
    address_line2 VARCHAR(500) DEFAULT '',
    city VARCHAR(100) DEFAULT '',
    state VARCHAR(100) DEFAULT '',
    postal_code VARCHAR(20) DEFAULT '',
    country VARCHAR(100) DEFAULT 'US',
    jobber_timezone VARCHAR(50) DEFAULT '',
    jobber_language VARCHAR(10) DEFAULT 'en',
    status VARCHAR(20) DEFAULT 'active',
    properties_json JSON DEFAULT '{}',
    created_at DATETIME,
    updated_at DATETIME,
    synced_at DATETIME,
    UNIQUE(company_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_jobber_clients_company_id ON jobber_clients(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_clients_external_id ON jobber_clients(external_id);

CREATE TABLE IF NOT EXISTS jobber_quotes (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    external_id VARCHAR(128) NOT NULL,
    client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
    name VARCHAR(255) DEFAULT '',
    description TEXT DEFAULT '',
    status VARCHAR(50) DEFAULT 'draft',
    amount FLOAT,
    currency VARCHAR(3) DEFAULT 'USD',
    tax_amount FLOAT,
    discount_amount FLOAT,
    sent_at DATETIME,
    accepted_at DATETIME,
    rejected_at DATETIME,
    expires_at DATETIME,
    estimate_id VARCHAR(36) REFERENCES estimates(id) ON DELETE SET NULL,
    line_items_json JSON DEFAULT '[]',
    properties_json JSON DEFAULT '{}',
    created_at DATETIME,
    updated_at DATETIME,
    synced_at DATETIME,
    UNIQUE(company_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_jobber_quotes_company_id ON jobber_quotes(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_quotes_external_id ON jobber_quotes(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_quotes_client_id ON jobber_quotes(client_id);

CREATE TABLE IF NOT EXISTS jobber_jobs (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    external_id VARCHAR(128) NOT NULL,
    client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
    name VARCHAR(255) DEFAULT '',
    description TEXT DEFAULT '',
    status VARCHAR(50) DEFAULT 'draft',
    project_type VARCHAR(100) DEFAULT '',
    estimated_revenue FLOAT,
    actual_revenue FLOAT,
    estimated_cost FLOAT,
    actual_cost FLOAT,
    start_date DATETIME,
    end_date DATETIME,
    estimated_end_date DATETIME,
    address_line1 VARCHAR(500) DEFAULT '',
    address_line2 VARCHAR(500) DEFAULT '',
    city VARCHAR(100) DEFAULT '',
    state VARCHAR(100) DEFAULT '',
    postal_code VARCHAR(20) DEFAULT '',
    estimate_id VARCHAR(36) REFERENCES estimates(id) ON DELETE SET NULL,
    quote_id VARCHAR(36) REFERENCES jobber_quotes(id) ON DELETE SET NULL,
    properties_json JSON DEFAULT '{}',
    created_at DATETIME,
    updated_at DATETIME,
    synced_at DATETIME,
    UNIQUE(company_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_jobber_jobs_company_id ON jobber_jobs(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_jobs_external_id ON jobber_jobs(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_jobs_client_id ON jobber_jobs(client_id);

CREATE TABLE IF NOT EXISTS jobber_invoices (
    id VARCHAR(36) PRIMARY KEY,
    company_id VARCHAR(36) NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    external_id VARCHAR(128) NOT NULL,
    client_id VARCHAR(36) REFERENCES jobber_clients(id) ON DELETE SET NULL,
    job_id VARCHAR(36) REFERENCES jobber_jobs(id) ON DELETE SET NULL,
    invoice_number VARCHAR(100) DEFAULT '',
    status VARCHAR(50) DEFAULT 'draft',
    amount FLOAT,
    currency VARCHAR(3) DEFAULT 'USD',
    tax_amount FLOAT,
    paid_amount FLOAT DEFAULT 0.0,
    issued_at DATETIME,
    due_date DATETIME,
    paid_at DATETIME,
    line_items_json JSON DEFAULT '[]',
    properties_json JSON DEFAULT '{}',
    created_at DATETIME,
    updated_at DATETIME,
    synced_at DATETIME,
    UNIQUE(company_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_jobber_invoices_company_id ON jobber_invoices(company_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_external_id ON jobber_invoices(external_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_client_id ON jobber_invoices(client_id);
CREATE INDEX IF NOT EXISTS idx_jobber_invoices_job_id ON jobber_invoices(job_id);
