SQL 006_jobber_tables.sql

-- =============================================================================
-- P2: Jobber Connector Tables
-- =============================================================================

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 TEXT DEFAULT '{}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    synced_at DATETIME DEFAULT NULL,
    UNIQUE (company_id, external_id)
);

CREATE INDEX idx_jobber_clients_company_id ON jobber_clients(company_id);
CREATE INDEX 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) DEFAULT NULL REFERENCES jobber_clients(id) ON DELETE SET NULL,
    name VARCHAR(255) DEFAULT '',
    description TEXT DEFAULT '',
    status VARCHAR(50) DEFAULT 'draft',
    amount FLOAT DEFAULT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    tax_amount FLOAT DEFAULT NULL,
    discount_amount FLOAT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    accepted_at DATETIME DEFAULT NULL,
    rejected_at DATETIME DEFAULT NULL,
    expires_at DATETIME DEFAULT NULL,
    estimate_id VARCHAR(36) DEFAULT NULL REFERENCES estimates(id) ON DELETE SET NULL,
    line_items_json TEXT DEFAULT '[]',
    properties_json TEXT DEFAULT '{}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    synced_at DATETIME DEFAULT NULL,
    UNIQUE (company_id, external_id)
);

CREATE INDEX idx_jobber_quotes_company_id ON jobber_quotes(company_id);
CREATE INDEX idx_jobber_quotes_external_id ON jobber_quotes(external_id);
CREATE INDEX idx_jobber_quotes_client_id ON jobber_quotes(client_id);
CREATE INDEX idx_jobber_quotes_estimate_id ON jobber_quotes(estimate_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) DEFAULT NULL 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 DEFAULT NULL,
    actual_revenue FLOAT DEFAULT NULL,
    estimated_cost FLOAT DEFAULT NULL,
    actual_cost FLOAT DEFAULT NULL,
    start_date DATETIME DEFAULT NULL,
    end_date DATETIME DEFAULT NULL,
    estimated_end_date DATETIME DEFAULT NULL,
    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) DEFAULT NULL REFERENCES estimates(id) ON DELETE SET NULL,
    quote_id VARCHAR(36) DEFAULT NULL REFERENCES jobber_quotes(id) ON DELETE SET NULL,
    properties_json TEXT DEFAULT '{}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    synced_at DATETIME DEFAULT NULL,
    UNIQUE (company_id, external_id)
);

CREATE INDEX idx_jobber_jobs_company_id ON jobber_jobs(company_id);
CREATE INDEX idx_jobber_jobs_external_id ON jobber_jobs(external_id);
CREATE INDEX idx_jobber_jobs_client_id ON jobber_jobs(client_id);
CREATE INDEX idx_jobber_jobs_estimate_id ON jobber_jobs(estimate_id);
CREATE INDEX idx_jobber_jobs_quote_id ON jobber_jobs(quote_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) DEFAULT NULL REFERENCES jobber_clients(id) ON DELETE SET NULL,
    job_id VARCHAR(36) DEFAULT NULL REFERENCES jobber_jobs(id) ON DELETE SET NULL,
    invoice_number VARCHAR(100) DEFAULT '',
    status VARCHAR(50) DEFAULT 'draft',
    amount FLOAT DEFAULT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    tax_amount FLOAT DEFAULT NULL,
    paid_amount FLOAT DEFAULT 0.0,
    issued_at DATETIME DEFAULT NULL,
    due_date DATETIME DEFAULT NULL,
    paid_at DATETIME DEFAULT NULL,
    line_items_json TEXT DEFAULT '[]',
    properties_json TEXT DEFAULT '{}',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    synced_at DATETIME DEFAULT NULL,
    UNIQUE (company_id, external_id)
);

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