ALTER TABLE customers
    ADD COLUMN IF NOT EXISTS is_bmc_customer BOOLEAN NOT NULL DEFAULT TRUE,
    ADD COLUMN IF NOT EXISTS show_in_customer_list BOOLEAN NOT NULL DEFAULT TRUE;

CREATE TABLE IF NOT EXISTS customer_house_organizations (
    id SERIAL PRIMARY KEY,
    host_customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    external_customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    location_id INTEGER REFERENCES locations_locations(id) ON DELETE SET NULL,
    relationship_type VARCHAR(40) NOT NULL DEFAULT 'house_user_company',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    notes TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (host_customer_id, external_customer_id, location_id)
);

CREATE TABLE IF NOT EXISTS contact_location_users (
    id SERIAL PRIMARY KEY,
    contact_id INTEGER NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
    host_customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    location_id INTEGER REFERENCES locations_locations(id) ON DELETE SET NULL,
    external_customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    relation_type VARCHAR(40) NOT NULL DEFAULT 'house_user',
    role VARCHAR(120),
    notes TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_customer_house_organizations_host
    ON customer_house_organizations(host_customer_id) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_contact_location_users_host
    ON contact_location_users(host_customer_id) WHERE is_active = TRUE;