-- 0002 — Phase 2 master data, branch database (SQLite). Same logical schema as
-- migrations/central/0002 minus centre-only tables (devices, sync_changes) —
-- these rows arrive via /api/sync/pull and are applied by the branch service.
-- role_assignments.branch_id keeps a soft reference here: SQLite cannot ADD
-- CONSTRAINT, and the centre validated the row before it synced down.

CREATE TABLE buildings (
    id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    address TEXT NULL,
    city TEXT NOT NULL,
    operator_contact TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE building_offices (
    id TEXT NOT NULL PRIMARY KEY,
    building_id TEXT NOT NULL REFERENCES buildings (id),
    code TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    UNIQUE (building_id, code)
);

CREATE TABLE branches (
    id TEXT NOT NULL PRIMARY KEY,
    code TEXT NOT NULL UNIQUE,
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    is_main INTEGER NOT NULL DEFAULT 0,
    region TEXT NOT NULL,
    building_id TEXT NULL REFERENCES buildings (id),
    timezone TEXT NOT NULL DEFAULT 'Asia/Baghdad',
    currencies_accepted TEXT NOT NULL DEFAULT '["IQD"]',
    business_day_cutoff TEXT NOT NULL DEFAULT '04:00',
    opening_hours TEXT NULL,
    cash_rounding_increment INTEGER NOT NULL DEFAULT 250,
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'closed')),
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE calendar_exceptions (
    id TEXT NOT NULL PRIMARY KEY,
    scope TEXT NOT NULL CHECK (scope IN ('ORG', 'BRANCH')),
    branch_id TEXT NULL REFERENCES branches (id),
    date_from TEXT NOT NULL,
    date_to TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('CLOSED', 'REDUCED_HOURS')),
    reason TEXT NOT NULL CHECK (reason IN ('owner_declared', 'public_holiday', 'religious_holiday', 'maintenance', 'security', 'force_majeure')),
    is_paid_for_staff INTEGER NOT NULL DEFAULT 1,
    is_retroactive INTEGER NOT NULL DEFAULT 0,
    declared_by_user_id TEXT NOT NULL,
    declared_at_utc TEXT NOT NULL,
    annulled_at_utc TEXT NULL,
    annulled_by_user_id TEXT NULL,
    note TEXT NULL,
    CHECK ((scope = 'ORG' AND branch_id IS NULL) OR (scope = 'BRANCH' AND branch_id IS NOT NULL)),
    CHECK (date_from <= date_to)
);

CREATE INDEX ix_calendar_dates ON calendar_exceptions (date_from, date_to);

CREATE TABLE categories (
    id TEXT NOT NULL PRIMARY KEY,
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    color TEXT NULL,
    icon TEXT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE items (
    id TEXT NOT NULL PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    category_id TEXT NOT NULL REFERENCES categories (id),
    item_type TEXT NOT NULL CHECK (item_type IN ('prepared', 'retail')),
    prep_station TEXT NOT NULL DEFAULT 'bar' CHECK (prep_station IN ('bar', 'kitchen', 'none')),
    is_stock_tracked INTEGER NOT NULL DEFAULT 0,
    tax_class TEXT NULL,
    image_ref TEXT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE modifier_groups (
    id TEXT NOT NULL PRIMARY KEY,
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    selection_type TEXT NOT NULL CHECK (selection_type IN ('single', 'multiple')),
    min_select INTEGER NOT NULL DEFAULT 0,
    max_select INTEGER NOT NULL DEFAULT 1,
    is_required INTEGER NOT NULL DEFAULT 0,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL,
    CHECK (min_select <= max_select)
);

CREATE TABLE modifier_options (
    id TEXT NOT NULL PRIMARY KEY,
    group_id TEXT NOT NULL REFERENCES modifier_groups (id),
    name_ar TEXT NOT NULL,
    name_en TEXT NOT NULL,
    price_delta_amount INTEGER NOT NULL DEFAULT 0,
    price_delta_currency TEXT NOT NULL DEFAULT 'IQD',
    is_default INTEGER NOT NULL DEFAULT 0,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE item_modifier_groups (
    id TEXT NOT NULL PRIMARY KEY,
    item_id TEXT NOT NULL REFERENCES items (id),
    group_id TEXT NOT NULL REFERENCES modifier_groups (id),
    is_required_override INTEGER NULL,
    default_option_ids TEXT NULL,
    sort_order INTEGER NOT NULL DEFAULT 0,
    UNIQUE (item_id, group_id)
);

CREATE TABLE item_prices (
    id TEXT NOT NULL PRIMARY KEY,
    item_id TEXT NOT NULL REFERENCES items (id),
    branch_id TEXT NULL REFERENCES branches (id),
    amount INTEGER NOT NULL,
    currency TEXT NOT NULL DEFAULT 'IQD',
    effective_from TEXT NOT NULL,
    created_at_utc TEXT NOT NULL,
    created_by_user_id TEXT NOT NULL
);

CREATE INDEX ix_item_prices_lookup ON item_prices (item_id, branch_id, effective_from);

CREATE TABLE modifier_option_prices (
    id TEXT NOT NULL PRIMARY KEY,
    option_id TEXT NOT NULL REFERENCES modifier_options (id),
    branch_id TEXT NULL REFERENCES branches (id),
    delta_amount INTEGER NOT NULL,
    currency TEXT NOT NULL DEFAULT 'IQD',
    effective_from TEXT NOT NULL,
    created_at_utc TEXT NOT NULL,
    created_by_user_id TEXT NOT NULL
);

CREATE INDEX ix_mop_lookup ON modifier_option_prices (option_id, branch_id, effective_from);

CREATE TABLE branch_item_settings (
    id TEXT NOT NULL PRIMARY KEY,
    branch_id TEXT NOT NULL REFERENCES branches (id),
    item_id TEXT NOT NULL REFERENCES items (id),
    is_available INTEGER NOT NULL DEFAULT 1,
    is_86d INTEGER NOT NULL DEFAULT 0,
    is_86d_until TEXT NULL,
    updated_at_utc TEXT NOT NULL,
    updated_by_user_id TEXT NOT NULL,
    UNIQUE (branch_id, item_id)
);

CREATE TABLE branch_option_settings (
    id TEXT NOT NULL PRIMARY KEY,
    branch_id TEXT NOT NULL REFERENCES branches (id),
    option_id TEXT NOT NULL REFERENCES modifier_options (id),
    is_86d INTEGER NOT NULL DEFAULT 0,
    is_86d_until TEXT NULL,
    updated_at_utc TEXT NOT NULL,
    updated_by_user_id TEXT NOT NULL,
    UNIQUE (branch_id, option_id)
);

CREATE TABLE recipe_lines (
    id TEXT NOT NULL PRIMARY KEY,
    item_id TEXT NOT NULL REFERENCES items (id),
    ingredient_ref TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    unit TEXT NOT NULL CHECK (unit IN ('g', 'ml', 'piece')),
    is_optional INTEGER NOT NULL DEFAULT 0,
    yield_portions INTEGER NULL,
    effective_from TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at_utc TEXT NOT NULL
);

CREATE TABLE fx_rates (
    id TEXT NOT NULL PRIMARY KEY,
    base_currency TEXT NOT NULL DEFAULT 'USD',
    quote_currency TEXT NOT NULL DEFAULT 'IQD',
    rate TEXT NOT NULL,
    branch_id TEXT NULL REFERENCES branches (id),
    effective_from TEXT NOT NULL,
    created_at_utc TEXT NOT NULL,
    created_by_user_id TEXT NOT NULL
);

CREATE INDEX ix_fx_lookup ON fx_rates (base_currency, quote_currency, branch_id, effective_from);
