-- Import Data schema for /api/v1/import-data
-- Order follows FK dependencies: leads, customers, then contacts/quotations/billings, then line tables, then sync_meta.
-- Run against Cloud SQL (PostgreSQL). Use run_import_data_schema.py or execute this file manually.

-- 1. leads (no FK)
CREATE TABLE IF NOT EXISTS leads (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     VARCHAR(50) NOT NULL,
    lead_source     VARCHAR(50) NOT NULL CHECK (lead_source IN (
        'erp','website_newsletter','facebook_leadgen','name_card','event_seminar'
    )),
    full_name       VARCHAR(255),
    email           VARCHAR(255),
    phone           VARCHAR(50),
    company         VARCHAR(255),
    title           VARCHAR(255),
    note            TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(255),
    CONSTRAINT leads_name_or_email CHECK (
        (TRIM(COALESCE(full_name,'')) <> '') OR (TRIM(COALESCE(email,'')) <> '')
    )
);
CREATE INDEX IF NOT EXISTS idx_leads_property_created ON leads(property_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_leads_property_source ON leads(property_id, lead_source);

-- 2. customers (no FK to other import_data tables)
CREATE TABLE IF NOT EXISTS customers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     VARCHAR(50) NOT NULL,
    full_name       VARCHAR(255) NOT NULL,
    title           VARCHAR(255),
    email           VARCHAR(255),
    phone           VARCHAR(50),
    company         VARCHAR(255),
    customer_type   VARCHAR(20) NOT NULL DEFAULT 'individual'
        CHECK (customer_type IN ('individual','company')),
    notes           TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(255),
    source          VARCHAR(20) DEFAULT 'file'
);
CREATE INDEX IF NOT EXISTS idx_customers_property_created ON customers(property_id, created_at DESC);

-- 3a. contacts (FK -> customers)
CREATE TABLE IF NOT EXISTS contacts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     VARCHAR(50) NOT NULL,
    customer_id     UUID REFERENCES customers(id) ON DELETE SET NULL,
    full_name       VARCHAR(255),
    email           VARCHAR(255),
    phone           VARCHAR(50),
    company         VARCHAR(255),
    title           VARCHAR(255),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(255),
    source          VARCHAR(20) DEFAULT 'file'
);
CREATE INDEX IF NOT EXISTS idx_contacts_property_created ON contacts(property_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_contacts_customer_id ON contacts(customer_id) WHERE customer_id IS NOT NULL;

-- 3b. quotations (FK -> customers)
CREATE TABLE IF NOT EXISTS quotations (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id       VARCHAR(50) NOT NULL,
    quotation_number  VARCHAR(100) NOT NULL,
    customer_id       UUID REFERENCES customers(id) ON DELETE SET NULL,
    customer          VARCHAR(255) NOT NULL,
    amount            NUMERIC(18,2) NOT NULL DEFAULT 0,
    status            VARCHAR(30) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','approved','rejected')),
    issue_date        DATE,
    valid_until       DATE,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by        VARCHAR(255),
    source            VARCHAR(20) DEFAULT 'file',
    UNIQUE(property_id, quotation_number)
);
CREATE INDEX IF NOT EXISTS idx_quotations_property_created ON quotations(property_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_quotations_customer_id ON quotations(customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_quotations_status ON quotations(property_id, status);

-- 3c. billings (FK -> customers)
CREATE TABLE IF NOT EXISTS billings (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id     VARCHAR(50) NOT NULL,
    invoice_number  VARCHAR(100) NOT NULL,
    customer_id     UUID REFERENCES customers(id) ON DELETE SET NULL,
    customer        VARCHAR(255) NOT NULL,
    amount          NUMERIC(18,2) NOT NULL DEFAULT 0,
    status          VARCHAR(30) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','paid','overdue','cancelled')),
    invoice_date    DATE,
    due_date        DATE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(255),
    source          VARCHAR(20) DEFAULT 'file',
    UNIQUE(property_id, invoice_number)
);
CREATE INDEX IF NOT EXISTS idx_billings_property_created ON billings(property_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_billings_customer_id ON billings(customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_billings_status ON billings(property_id, status);

-- 4a. quotation_lines (FK -> quotations)
CREATE TABLE IF NOT EXISTS quotation_lines (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quotation_id      UUID NOT NULL REFERENCES quotations(id) ON DELETE CASCADE,
    property_id       VARCHAR(50) NOT NULL,
    line_no           INTEGER NOT NULL,
    product           VARCHAR(255),
    product_sku       VARCHAR(100),
    qty               NUMERIC(18,4) NOT NULL DEFAULT 0,
    unit_price        NUMERIC(18,4) NOT NULL DEFAULT 0,
    line_amount       NUMERIC(18,2) NOT NULL DEFAULT 0,
    category          VARCHAR(255),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_quotation_lines_quotation ON quotation_lines(quotation_id);
CREATE INDEX IF NOT EXISTS idx_quotation_lines_property_category ON quotation_lines(property_id, category);

-- 4b. billing_lines (FK -> billings)
CREATE TABLE IF NOT EXISTS billing_lines (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    billing_id      UUID NOT NULL REFERENCES billings(id) ON DELETE CASCADE,
    property_id     VARCHAR(50) NOT NULL,
    line_no         INTEGER NOT NULL,
    product         VARCHAR(255),
    product_sku     VARCHAR(100),
    qty             NUMERIC(18,4) NOT NULL DEFAULT 0,
    unit_price      NUMERIC(18,4) NOT NULL DEFAULT 0,
    line_amount     NUMERIC(18,2) NOT NULL DEFAULT 0,
    category        VARCHAR(255),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_billing_lines_billing ON billing_lines(billing_id);
CREATE INDEX IF NOT EXISTS idx_billing_lines_property_category ON billing_lines(property_id, category);

-- 5. sync_meta (no FK)
CREATE TABLE IF NOT EXISTS sync_meta (
    property_id     VARCHAR(50) NOT NULL,
    entity          VARCHAR(30) NOT NULL,
    last_import_at  TIMESTAMPTZ,
    last_sync_at    TIMESTAMPTZ,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (property_id, entity)
);
