-- PostgreSQL Initial Schema for Autotask Sync
-- This migration creates all entity tables with audit fields, foreign keys, and sync_history table

-- Enable UUID extension if needed
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================================
-- CORE ENTITY TABLES
-- ============================================================================

-- Companies table
CREATE TABLE IF NOT EXISTS companies (
    id BIGINT PRIMARY KEY,
    company_name VARCHAR(255),
    company_number VARCHAR(100),
    phone VARCHAR(50),
    fax VARCHAR(50),
    website VARCHAR(255),
    address1 VARCHAR(255),
    address2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    company_type INTEGER,
    owner_resource_id BIGINT,
    territory_id BIGINT,
    market_segment_id BIGINT,
    competitor_id BIGINT,
    billing_address1 VARCHAR(255),
    billing_address2 VARCHAR(255),
    billing_city VARCHAR(100),
    billing_state VARCHAR(50),
    billing_postal_code VARCHAR(20),
    billing_country VARCHAR(100),
    tax_id VARCHAR(50),
    tax_exempt BOOLEAN DEFAULT false,
    tax_region_id BIGINT,
    currency_id INTEGER,
    invoice_method INTEGER,
    invoice_template_id BIGINT,
    quote_template_id BIGINT,
    key_account_icon INTEGER,
    last_activity_date TIMESTAMP,
    last_tracked_modification_date_time TIMESTAMP,
    api_vendor_id INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- Resources (Users) table
CREATE TABLE IF NOT EXISTS resources (
    id BIGINT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    user_name VARCHAR(100),
    title VARCHAR(100),
    office_phone VARCHAR(50),
    mobile_phone VARCHAR(50),
    office_extension VARCHAR(20),
    is_active BOOLEAN DEFAULT true,
    location_id BIGINT,
    resource_type INTEGER,
    pay_roll_identifier VARCHAR(100),
    hire_date DATE,
    travel_availability_pct DECIMAL(5,2),
    survey_resource_rating DECIMAL(3,2),
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- Contacts table
CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    title VARCHAR(100),
    email_address VARCHAR(255),
    email_address2 VARCHAR(255),
    email_address3 VARCHAR(255),
    phone VARCHAR(50),
    extension VARCHAR(20),
    alternate_phone VARCHAR(50),
    mobile_phone VARCHAR(50),
    fax VARCHAR(50),
    address_line VARCHAR(255),
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(20),
    country VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    name_prefix VARCHAR(20),
    name_suffix VARCHAR(20),
    facebook_url VARCHAR(255),
    twitter_url VARCHAR(255),
    linked_in_url VARCHAR(255),
    primary_contact BOOLEAN DEFAULT false,
    account_physical_location_id BIGINT,
    solicitation_opt_out BOOLEAN DEFAULT false,
    room_number VARCHAR(50),
    last_activity_date TIMESTAMP,
    last_modified_date TIMESTAMP,
    api_vendor_id INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
    id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    project_name VARCHAR(255),
    project_number VARCHAR(100),
    description TEXT,
    start_date_time TIMESTAMP,
    end_date_time TIMESTAMP,
    estimated_time DECIMAL(10,2),
    actual_hours DECIMAL(10,2),
    estimated_sale_cost DECIMAL(15,2),
    labor_estimated_costs DECIMAL(15,2),
    labor_estimated_revenue DECIMAL(15,2),
    project_cost_estimated_margin_percentage DECIMAL(5,2),
    status INTEGER,
    type INTEGER,
    project_lead_resource_id BIGINT,
    account_executive_resource_id BIGINT,
    owner_resource_id BIGINT,
    creator_resource_id BIGINT,
    completed_percentage DECIMAL(5,2),
    completed_date_time TIMESTAMP,
    duration INTEGER,
    original_estimated_revenue DECIMAL(15,2),
    estimated_time_cost DECIMAL(15,2),
    purchase_order_number VARCHAR(100),
    business_division_subdivision_id INTEGER,
    line_of_business_id BIGINT,
    department INTEGER,
    last_activity_date_time TIMESTAMP,
    last_activity_person_type INTEGER,
    last_activity_resource_id BIGINT,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (project_lead_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
    FOREIGN KEY (owner_resource_id) REFERENCES resources(id) ON DELETE SET NULL
);

-- Tickets table
CREATE TABLE IF NOT EXISTS tickets (
    id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    ticket_number VARCHAR(100),
    title VARCHAR(255),
    description TEXT,
    status INTEGER,
    priority INTEGER,
    queue_id INTEGER,
    issue_type INTEGER,
    sub_issue_type INTEGER,
    source INTEGER,
    assigned_resource_id BIGINT,
    assigned_resource_role_id BIGINT,
    contact_id BIGINT,
    account_physical_location_id BIGINT,
    due_date_time TIMESTAMP,
    estimated_hours DECIMAL(10,2),
    completed_date TIMESTAMP,
    create_date TIMESTAMP,
    created_by_contact_id BIGINT,
    last_activity_date TIMESTAMP,
    last_customer_notification_date_time TIMESTAMP,
    last_customer_visible_activity_date_time TIMESTAMP,
    first_response_date_time TIMESTAMP,
    resolution_plan_date_time TIMESTAMP,
    resolved_date_time TIMESTAMP,
    first_response_assigned_resource_id BIGINT,
    first_response_initiating_resource_id BIGINT,
    project_id BIGINT,
    opportunity_id BIGINT,
    change_approval_board INTEGER,
    change_approval_status INTEGER,
    change_approval_type INTEGER,
    change_info_field1 VARCHAR(255),
    change_info_field2 VARCHAR(255),
    change_info_field3 VARCHAR(255),
    change_info_field4 VARCHAR(255),
    change_info_field5 VARCHAR(255),
    contract_id BIGINT,
    monitor_id BIGINT,
    monitor_type_id INTEGER,
    ticket_type INTEGER,
    ticket_category INTEGER,
    service_level_agreement_id INTEGER,
    resolution TEXT,
    purchase_order_number VARCHAR(100),
    ticket_completion_date TIMESTAMP,
    last_activity_person_type INTEGER,
    last_activity_resource_id BIGINT,
    current_service_thermometer_rating INTEGER,
    previous_service_thermometer_rating INTEGER,
    service_thermometer_temperature INTEGER,
    api_vendor_id INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- Tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id BIGINT PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    status INTEGER,
    priority INTEGER,
    assigned_resource_id BIGINT,
    assigned_resource_role_id BIGINT,
    department_id INTEGER,
    estimated_hours DECIMAL(10,2),
    remaining_hours DECIMAL(10,2),
    hours_to_be_scheduled DECIMAL(10,2),
    start_date_time TIMESTAMP,
    end_date_time TIMESTAMP,
    completed_date_time TIMESTAMP,
    create_date_time TIMESTAMP,
    creator_resource_id BIGINT,
    completed_by_resource_id BIGINT,
    last_activity_date_time TIMESTAMP,
    project_id BIGINT,
    ticket_id BIGINT,
    phase_id BIGINT,
    allocation_code_id BIGINT,
    task_type INTEGER,
    task_is_billable BOOLEAN DEFAULT true,
    task_number VARCHAR(100),
    purchase_order_number VARCHAR(100),
    can_client_portal_user_complete_task BOOLEAN DEFAULT false,
    creator_type INTEGER,
    task_category_id INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (assigned_resource_id) REFERENCES resources(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);

-- Configuration Items table
CREATE TABLE IF NOT EXISTS configuration_items (
    id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    product_id BIGINT,
    reference_title VARCHAR(255),
    reference_number VARCHAR(100),
    serial_number VARCHAR(100),
    install_date DATE,
    warranty_expiration_date DATE,
    is_active BOOLEAN DEFAULT true,
    daily_cost DECIMAL(15,2),
    hourly_cost DECIMAL(15,2),
    monthly_cost DECIMAL(15,2),
    per_use_cost DECIMAL(15,2),
    setup_fee DECIMAL(15,2),
    contact_id BIGINT,
    location_id BIGINT,
    vendor_id BIGINT,
    installed_by_id BIGINT,
    installed_by_contact_id BIGINT,
    parent_configuration_item_id BIGINT,
    notes TEXT,
    create_date TIMESTAMP,
    created_by_person_id BIGINT,
    last_modified_time TIMESTAMP,
    last_activity_person_type INTEGER,
    impersonator_creator_resource_id BIGINT,
    configuration_item_category_id BIGINT,
    configuration_item_type INTEGER,
    datto_availability DECIMAL(5,2),
    datto_device_memory_megabytes BIGINT,
    datto_drives_errors BOOLEAN,
    datto_hostname VARCHAR(255),
    datto_internal_ip VARCHAR(50),
    datto_kernel_version_id BIGINT,
    datto_last_check_in_date_time TIMESTAMP,
    datto_nic_speed_kilobits_per_second BIGINT,
    datto_number_of_agents INTEGER,
    datto_number_of_drives INTEGER,
    datto_number_of_logical_volumes INTEGER,
    datto_number_of_volumes INTEGER,
    datto_off_site_storage_used_bytes BIGINT,
    datto_os_version_id BIGINT,
    datto_percentage_used DECIMAL(5,2),
    datto_protected_kilobytes BIGINT,
    datto_remote_ip VARCHAR(50),
    datto_serial_number VARCHAR(100),
    datto_uptime_seconds BIGINT,
    datto_used_kilobytes BIGINT,
    datto_z_pool_percentage DECIMAL(5,2),
    device_networking_id BIGINT,
    last_backup_date TIMESTAMP,
    last_backup_status INTEGER,
    os_version_id BIGINT,
    service_id BIGINT,
    service_bundle_id BIGINT,
    snmp_location VARCHAR(255),
    snmp_name VARCHAR(255),
    snmp_contact VARCHAR(255),
    api_vendor_id INTEGER,
    device_type VARCHAR(100),
    rmm_device_uid VARCHAR(255),
    rmm_device_audit_architecture_id BIGINT,
    rmm_device_audit_display_adaptor_id BIGINT,
    rmm_device_audit_domain_id BIGINT,
    rmm_device_audit_external_ip_address VARCHAR(50),
    rmm_device_audit_hostname VARCHAR(255),
    rmm_device_audit_ip_address VARCHAR(50),
    rmm_device_audit_mac_address VARCHAR(50),
    rmm_device_audit_manufacturer_id BIGINT,
    rmm_device_audit_missing_patch_count INTEGER,
    rmm_device_audit_mobile_network_operator_id BIGINT,
    rmm_device_audit_mobile_number VARCHAR(50),
    rmm_device_audit_model_id BIGINT,
    rmm_device_audit_motherboard_id BIGINT,
    rmm_device_audit_operating_system_id BIGINT,
    rmm_device_audit_processor_id BIGINT,
    rmm_device_audit_service_pack_id BIGINT,
    rmm_device_audit_snmp_contact VARCHAR(255),
    rmm_device_audit_snmp_location VARCHAR(255),
    rmm_device_audit_snmp_name VARCHAR(255),
    rmm_device_audit_software_status_id BIGINT,
    rmm_device_audit_storage_bytes BIGINT,
    rmm_open_alert_count INTEGER,
    rmm_device_audit_description VARCHAR(255),
    rmm_device_audit_device_type_id BIGINT,
    rmm_device_audit_last_user VARCHAR(255),
    rmm_device_audit_memory_bytes BIGINT,
    source_cost_id BIGINT,
    source_cost_type INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
);

-- Contracts table
CREATE TABLE IF NOT EXISTS contracts (
    id BIGINT PRIMARY KEY,
    company_id BIGINT NOT NULL,
    contract_name VARCHAR(255),
    contract_number VARCHAR(100),
    description TEXT,
    start_date DATE,
    end_date DATE,
    time_reporting_requires_start_and_stop_times INTEGER,
    service_level_agreement_id INTEGER,
    contract_type INTEGER,
    contract_category INTEGER,
    status INTEGER,
    business_division_subdivision_id INTEGER,
    contact_id BIGINT,
    contact_name VARCHAR(255),
    billing_preference INTEGER,
    purchase_order_number VARCHAR(100),
    setup_fee DECIMAL(15,2),
    setup_fee_allocation_code_id BIGINT,
    estimated_cost DECIMAL(15,2),
    estimated_hours DECIMAL(10,2),
    estimated_revenue DECIMAL(15,2),
    over_budget_dollar_amount DECIMAL(15,2),
    over_budget_hours DECIMAL(10,2),
    contract_period_type VARCHAR(50),
    opportunity_id BIGINT,
    renewed_contract_id BIGINT,
    is_default_contract BOOLEAN DEFAULT false,
    internal_currency_setup_fee DECIMAL(15,2),
    internal_currency_over_budget_dollar_amount DECIMAL(15,2),
    internal_currency_estimated_cost DECIMAL(15,2),
    internal_currency_estimated_revenue DECIMAL(15,2),
    exclusion_contract_id BIGINT,
    internal_currency_monthly_revenue DECIMAL(15,2),
    internal_currency_quarterly_revenue DECIMAL(15,2),
    internal_currency_semi_annual_revenue DECIMAL(15,2),
    internal_currency_yearly_revenue DECIMAL(15,2),
    internal_currency_one_time_revenue DECIMAL(15,2),
    compliance BOOLEAN,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
);

-- Billing Items table
CREATE TABLE IF NOT EXISTS billing_items (
    id BIGINT PRIMARY KEY,
    company_id BIGINT,
    product_id BIGINT,
    description TEXT,
    quantity DECIMAL(10,2),
    rate DECIMAL(15,2),
    total_amount DECIMAL(15,2),
    line_discount_dollars DECIMAL(15,2),
    line_discount_percent DECIMAL(5,2),
    tax_category_id INTEGER,
    internal_currency_line_discount_dollars DECIMAL(15,2),
    allocation_code_id BIGINT,
    invoice_id BIGINT,
    vendor_id BIGINT,
    expense_item BOOLEAN DEFAULT false,
    task_id BIGINT,
    ticket_id BIGINT,
    project_id BIGINT,
    our_cost DECIMAL(15,2),
    list_price DECIMAL(15,2),
    unit_cost DECIMAL(15,2),
    unit_price DECIMAL(15,2),
    extended_price DECIMAL(15,2),
    tax_dollars DECIMAL(15,2),
    internal_currency_unit_price DECIMAL(15,2),
    internal_currency_total_amount DECIMAL(15,2),
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE SET NULL,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- ============================================================================
-- PICKLIST TABLES
-- ============================================================================

-- Statuses picklist
CREATE TABLE IF NOT EXISTS statuses (
    value INTEGER PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    sort_order INTEGER,
    parent_value INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- Issue Types picklist
CREATE TABLE IF NOT EXISTS issue_types (
    value INTEGER PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    sort_order INTEGER,
    parent_value INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- Sub-Issue Types picklist
CREATE TABLE IF NOT EXISTS sub_issue_types (
    value INTEGER PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    sort_order INTEGER,
    parent_value INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- Work Types picklist
CREATE TABLE IF NOT EXISTS work_types (
    value INTEGER PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    is_system BOOLEAN DEFAULT false,
    sort_order INTEGER,
    parent_value INTEGER,
    -- Audit fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMP
);

-- ============================================================================
-- SYNC HISTORY TABLE
-- ============================================================================

CREATE TABLE IF NOT EXISTS sync_history (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,
    sync_type VARCHAR(50) NOT NULL CHECK (sync_type IN ('full', 'incremental', 'entity-specific')),
    status VARCHAR(50) NOT NULL CHECK (status IN ('started', 'in_progress', 'completed', 'failed')),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    records_added INTEGER DEFAULT 0,
    records_updated INTEGER DEFAULT 0,
    records_deleted INTEGER DEFAULT 0,
    error_message TEXT,
    triggered_by VARCHAR(255)
);

-- ============================================================================
-- INDEXES
-- ============================================================================

-- Companies indexes
CREATE INDEX IF NOT EXISTS idx_companies_is_active ON companies(is_active);
CREATE INDEX IF NOT EXISTS idx_companies_is_deleted ON companies(is_deleted);
CREATE INDEX IF NOT EXISTS idx_companies_synced_at ON companies(synced_at);

-- Resources indexes
CREATE INDEX IF NOT EXISTS idx_resources_email ON resources(email);
CREATE INDEX IF NOT EXISTS idx_resources_is_active ON resources(is_active);
CREATE INDEX IF NOT EXISTS idx_resources_is_deleted ON resources(is_deleted);

-- Contacts indexes
CREATE INDEX IF NOT EXISTS idx_contacts_company_id ON contacts(company_id);
CREATE INDEX IF NOT EXISTS idx_contacts_email_address ON contacts(email_address);
CREATE INDEX IF NOT EXISTS idx_contacts_is_active ON contacts(is_active);
CREATE INDEX IF NOT EXISTS idx_contacts_is_deleted ON contacts(is_deleted);

-- Projects indexes
CREATE INDEX IF NOT EXISTS idx_projects_company_id ON projects(company_id);
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_projects_is_deleted ON projects(is_deleted);

-- Tickets indexes
CREATE INDEX IF NOT EXISTS idx_tickets_company_id ON tickets(company_id);
CREATE INDEX IF NOT EXISTS idx_tickets_assigned_resource_id ON tickets(assigned_resource_id);
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);
CREATE INDEX IF NOT EXISTS idx_tickets_contact_id ON tickets(contact_id);
CREATE INDEX IF NOT EXISTS idx_tickets_is_deleted ON tickets(is_deleted);
CREATE INDEX IF NOT EXISTS idx_tickets_create_date ON tickets(create_date);

-- Tasks indexes
CREATE INDEX IF NOT EXISTS idx_tasks_assigned_resource_id ON tasks(assigned_resource_id);
CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id);
CREATE INDEX IF NOT EXISTS idx_tasks_ticket_id ON tasks(ticket_id);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_is_deleted ON tasks(is_deleted);

-- Configuration Items indexes
CREATE INDEX IF NOT EXISTS idx_config_items_company_id ON configuration_items(company_id);
CREATE INDEX IF NOT EXISTS idx_config_items_contact_id ON configuration_items(contact_id);
CREATE INDEX IF NOT EXISTS idx_config_items_serial_number ON configuration_items(serial_number);
CREATE INDEX IF NOT EXISTS idx_config_items_is_active ON configuration_items(is_active);
CREATE INDEX IF NOT EXISTS idx_config_items_is_deleted ON configuration_items(is_deleted);
CREATE INDEX IF NOT EXISTS idx_config_items_rmm_device_uid ON configuration_items(rmm_device_uid);

-- Contracts indexes
CREATE INDEX IF NOT EXISTS idx_contracts_company_id ON contracts(company_id);
CREATE INDEX IF NOT EXISTS idx_contracts_status ON contracts(status);
CREATE INDEX IF NOT EXISTS idx_contracts_is_deleted ON contracts(is_deleted);

-- Billing Items indexes
CREATE INDEX IF NOT EXISTS idx_billing_items_company_id ON billing_items(company_id);
CREATE INDEX IF NOT EXISTS idx_billing_items_task_id ON billing_items(task_id);
CREATE INDEX IF NOT EXISTS idx_billing_items_ticket_id ON billing_items(ticket_id);
CREATE INDEX IF NOT EXISTS idx_billing_items_project_id ON billing_items(project_id);
CREATE INDEX IF NOT EXISTS idx_billing_items_is_deleted ON billing_items(is_deleted);

-- Sync History indexes
CREATE INDEX IF NOT EXISTS idx_sync_history_entity_type ON sync_history(entity_type);
CREATE INDEX IF NOT EXISTS idx_sync_history_status ON sync_history(status);
CREATE INDEX IF NOT EXISTS idx_sync_history_started_at ON sync_history(started_at DESC);

-- ============================================================================
-- COMMENTS
-- ============================================================================

COMMENT ON TABLE companies IS 'Autotask companies/accounts';
COMMENT ON TABLE resources IS 'Autotask resources (users/technicians)';
COMMENT ON TABLE contacts IS 'Company contacts';
COMMENT ON TABLE projects IS 'Autotask projects';
COMMENT ON TABLE tickets IS 'Autotask service tickets';
COMMENT ON TABLE tasks IS 'Autotask tasks';
COMMENT ON TABLE configuration_items IS 'Configuration items (devices/assets)';
COMMENT ON TABLE contracts IS 'Service contracts';
COMMENT ON TABLE billing_items IS 'Billing/invoice line items';
COMMENT ON TABLE sync_history IS 'Tracks all sync operations from Autotask';

COMMENT ON COLUMN companies.is_deleted IS 'Soft delete flag - true if deleted in Autotask';
COMMENT ON COLUMN companies.synced_at IS 'Last time this record was synced from Autotask';
COMMENT ON COLUMN sync_history.sync_type IS 'Type of sync: full, incremental, or entity-specific';
COMMENT ON COLUMN sync_history.status IS 'Current status: started, in_progress, completed, or failed';