-- Migration 003: Create customer hierarchy tables
-- Customer is the top-level business entity
-- CustomerAddress and CustomerContact are for customers without accounts

-- Top-level business entity
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    name VARCHAR(200) NOT NULL UNIQUE,
    status entity_status NOT NULL DEFAULT 'ACTIVE',

    start_date DATE,
    end_date DATE,

    -- Billing info
    billing_terms TEXT,
    billing_email VARCHAR(254),

    -- Wave accounting integration
    wave_customer_id VARCHAR(255)
);

-- Customer addresses (for customers without accounts)
CREATE TABLE customer_addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,

    name VARCHAR(200),
    street_address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    zip_code VARCHAR(20) NOT NULL,

    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,

    notes TEXT
);

-- Customer contacts
CREATE TABLE customer_contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,

    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(254),

    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,

    notes TEXT
);

-- M2M: CustomerProfile access to Customer (determines data authorization)
CREATE TABLE customer_profile_access (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    customer_profile_id UUID NOT NULL REFERENCES customer_profiles(id) ON DELETE CASCADE,
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,

    UNIQUE (customer_profile_id, customer_id)
);

-- Indexes
CREATE INDEX idx_customers_status ON customers(status);
CREATE INDEX idx_customer_addresses_customer ON customer_addresses(customer_id);
CREATE INDEX idx_customer_contacts_customer ON customer_contacts(customer_id);
CREATE INDEX idx_customer_profile_access_profile ON customer_profile_access(customer_profile_id);
CREATE INDEX idx_customer_profile_access_customer ON customer_profile_access(customer_id);