CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    portal_tenant_id VARCHAR(80) UNIQUE,
    portal_tenant_slug VARCHAR(120),
    layout_policy_applied BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE venue_booking_settings (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER NOT NULL UNIQUE REFERENCES venues(id),
    turn_duration_minutes INTEGER NOT NULL DEFAULT 120,
    whatsapp_assistant_prompt TEXT NOT NULL DEFAULT '',
    whatsapp_business_account_id TEXT NOT NULL DEFAULT '',
    whatsapp_business_id TEXT NOT NULL DEFAULT '',
    whatsapp_phone_number_id TEXT NOT NULL DEFAULT '',
    whatsapp_access_token TEXT NOT NULL DEFAULT '',
    whatsapp_display_phone_number TEXT NOT NULL DEFAULT '',
    whatsapp_verified_name TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX uq_venue_booking_settings_whatsapp_phone_number_id
    ON venue_booking_settings (whatsapp_phone_number_id)
    WHERE whatsapp_phone_number_id <> '';

CREATE TABLE rooms (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER NOT NULL REFERENCES venues(id),
    name VARCHAR(120) NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    counter_name VARCHAR(120),
    counter_x INTEGER,
    counter_y INTEGER,
    counter_width INTEGER,
    counter_height INTEGER,
    counter_visible BOOLEAN NOT NULL DEFAULT TRUE,
    entrance_name VARCHAR(120),
    entrance_x INTEGER,
    entrance_y INTEGER,
    entrance_width INTEGER,
    entrance_height INTEGER,
    entrance_visible BOOLEAN NOT NULL DEFAULT TRUE,
    background_image_data_url TEXT
);

CREATE TABLE tables (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL REFERENCES rooms(id),
    name VARCHAR(80) NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    shape VARCHAR(20) NOT NULL DEFAULT 'square',
    rotation_degrees DOUBLE PRECISION NOT NULL DEFAULT 0,
    min_seats INTEGER NOT NULL,
    max_seats INTEGER NOT NULL,
    join_group VARCHAR(80),
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE table_combinations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL REFERENCES rooms(id),
    name VARCHAR(120) NOT NULL,
    table_ids JSON NOT NULL,
    min_seats INTEGER NOT NULL,
    max_seats INTEGER NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TYPE reservation_status AS ENUM (
    'pending',
    'confirmed',
    'seated',
    'completed',
    'cancelled',
    'no_show'
);

CREATE TYPE reservation_source AS ENUM (
    'manual',
    'whatsapp',
    'web'
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    phone VARCHAR(40) NOT NULL,
    email VARCHAR(160),
    notes TEXT
);

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER NOT NULL REFERENCES venues(id),
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    reservation_date DATE NOT NULL,
    start_time TIME NOT NULL,
    duration_minutes INTEGER NOT NULL,
    guests INTEGER NOT NULL,
    status reservation_status NOT NULL,
    source reservation_source NOT NULL,
    notes TEXT,
    area_preference VARCHAR(120),
    assigned_table_id INTEGER REFERENCES tables(id),
    assigned_combination_id INTEGER REFERENCES table_combinations(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE whatsapp_booking_sessions (
    id SERIAL PRIMARY KEY,
    contact_phone VARCHAR(40) NOT NULL,
    venue_id INTEGER NOT NULL REFERENCES venues(id),
    status VARCHAR(40) NOT NULL,
    draft JSON NOT NULL,
    reservation_id INTEGER REFERENCES reservations(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (contact_phone, venue_id)
);

CREATE TABLE whatsapp_contacts (
    id SERIAL PRIMARY KEY,
    contact_phone VARCHAR(40) NOT NULL UNIQUE,
    display_name VARCHAR(160) NOT NULL,
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE reservation_status_history (
    id SERIAL PRIMARY KEY,
    reservation_id INTEGER NOT NULL REFERENCES reservations(id),
    old_status reservation_status,
    new_status reservation_status NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE whatsapp_event_logs (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER REFERENCES venues(id),
    event_type VARCHAR(40) NOT NULL,
    contact_phone VARCHAR(40),
    wa_message_id VARCHAR(120),
    summary TEXT NOT NULL,
    payload JSON NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE whatsapp_assistant_turns (
    id SERIAL PRIMARY KEY,
    venue_id INTEGER NOT NULL REFERENCES venues(id),
    contact_phone VARCHAR(40) NOT NULL,
    role VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    assistant_route VARCHAR(80),
    assistant_model VARCHAR(160),
    source_wa_message_id VARCHAR(120),
    trace JSON NOT NULL DEFAULT '{}'::json,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ix_whatsapp_assistant_turns_venue_contact_created
    ON whatsapp_assistant_turns (venue_id, contact_phone, created_at DESC, id DESC);
