-- 0002 — Phase 2 master data (docs 01, 02, 10 §2/§3.1, 12 §9).
-- Organization structure, business calendar, catalog with versioned prices,
-- FX rates, branch devices (pull auth) and the sync change log.
-- audit_events deliberately carries NO foreign keys: an audit write must never
-- be blocked by referential state.

CREATE TABLE buildings (
    id CHAR(36) NOT NULL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    address VARCHAR(300) NULL,
    city VARCHAR(60) NOT NULL,
    operator_contact VARCHAR(200) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE building_offices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    building_id CHAR(36) NOT NULL,
    code VARCHAR(40) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_building_offices_building FOREIGN KEY (building_id) REFERENCES buildings (id),
    CONSTRAINT uq_building_offices UNIQUE (building_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branches (
    id CHAR(36) NOT NULL PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    is_main TINYINT NOT NULL DEFAULT 0,
    region VARCHAR(40) NOT NULL,
    building_id CHAR(36) NULL,
    timezone VARCHAR(40) NOT NULL DEFAULT 'Asia/Baghdad',
    currencies_accepted VARCHAR(100) NOT NULL DEFAULT '["IQD"]',
    business_day_cutoff CHAR(5) NOT NULL DEFAULT '04:00',
    opening_hours VARCHAR(100) NULL,
    cash_rounding_increment INT NOT NULL DEFAULT 250,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_branches_code UNIQUE (code),
    CONSTRAINT fk_branches_building FOREIGN KEY (building_id) REFERENCES buildings (id),
    CONSTRAINT ck_branches_status CHECK (status IN ('active', 'suspended', 'closed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE role_assignments
    ADD CONSTRAINT fk_role_assignments_branch FOREIGN KEY (branch_id) REFERENCES branches (id);

CREATE TABLE calendar_exceptions (
    id CHAR(36) NOT NULL PRIMARY KEY,
    scope VARCHAR(10) NOT NULL,
    branch_id CHAR(36) NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    reason VARCHAR(30) NOT NULL,
    is_paid_for_staff TINYINT NOT NULL DEFAULT 1,
    is_retroactive TINYINT NOT NULL DEFAULT 0,
    declared_by_user_id CHAR(36) NOT NULL,
    declared_at_utc DATETIME NOT NULL,
    annulled_at_utc DATETIME NULL,
    annulled_by_user_id CHAR(36) NULL,
    note TEXT NULL,
    CONSTRAINT fk_calendar_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_calendar_scope CHECK (scope IN ('ORG', 'BRANCH')),
    CONSTRAINT ck_calendar_scope_branch CHECK ((scope = 'ORG' AND branch_id IS NULL) OR (scope = 'BRANCH' AND branch_id IS NOT NULL)),
    CONSTRAINT ck_calendar_status CHECK (status IN ('CLOSED', 'REDUCED_HOURS')),
    CONSTRAINT ck_calendar_reason CHECK (reason IN ('owner_declared', 'public_holiday', 'religious_holiday', 'maintenance', 'security', 'force_majeure')),
    CONSTRAINT ck_calendar_range CHECK (date_from <= date_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_calendar_dates ON calendar_exceptions (date_from, date_to);

CREATE TABLE categories (
    id CHAR(36) NOT NULL PRIMARY KEY,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    color VARCHAR(20) NULL,
    icon VARCHAR(40) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE items (
    id CHAR(36) NOT NULL PRIMARY KEY,
    sku VARCHAR(40) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    category_id CHAR(36) NOT NULL,
    item_type VARCHAR(10) NOT NULL,
    prep_station VARCHAR(10) NOT NULL DEFAULT 'bar',
    is_stock_tracked TINYINT NOT NULL DEFAULT 0,
    tax_class VARCHAR(30) NULL,
    image_ref VARCHAR(300) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_items_sku UNIQUE (sku),
    CONSTRAINT fk_items_category FOREIGN KEY (category_id) REFERENCES categories (id),
    CONSTRAINT ck_items_type CHECK (item_type IN ('prepared', 'retail')),
    CONSTRAINT ck_items_station CHECK (prep_station IN ('bar', 'kitchen', 'none'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE modifier_groups (
    id CHAR(36) NOT NULL PRIMARY KEY,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    selection_type VARCHAR(10) NOT NULL,
    min_select INT NOT NULL DEFAULT 0,
    max_select INT NOT NULL DEFAULT 1,
    is_required TINYINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT ck_modifier_groups_type CHECK (selection_type IN ('single', 'multiple')),
    CONSTRAINT ck_modifier_groups_minmax CHECK (min_select <= max_select)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE modifier_options (
    id CHAR(36) NOT NULL PRIMARY KEY,
    group_id CHAR(36) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    price_delta_amount INT NOT NULL DEFAULT 0,
    price_delta_currency CHAR(3) NOT NULL DEFAULT 'IQD',
    is_default TINYINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_modifier_options_group FOREIGN KEY (group_id) REFERENCES modifier_groups (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE item_modifier_groups (
    id CHAR(36) NOT NULL PRIMARY KEY,
    item_id CHAR(36) NOT NULL,
    group_id CHAR(36) NOT NULL,
    is_required_override TINYINT NULL,
    default_option_ids TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_img_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_img_group FOREIGN KEY (group_id) REFERENCES modifier_groups (id),
    CONSTRAINT uq_item_modifier_groups UNIQUE (item_id, group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Versioned prices (doc 02 §4): branch_id NULL = org-wide base price. Resolution:
-- branch row else base row, highest effective_from <= target day, created_at tiebreak.
CREATE TABLE item_prices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    item_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NULL,
    amount INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    effective_from DATE NOT NULL,
    created_at_utc DATETIME NOT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    CONSTRAINT fk_item_prices_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_item_prices_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE modifier_option_prices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    option_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NULL,
    delta_amount INT NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'IQD',
    effective_from DATE NOT NULL,
    created_at_utc DATETIME NOT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    CONSTRAINT fk_mop_option FOREIGN KEY (option_id) REFERENCES modifier_options (id),
    CONSTRAINT fk_mop_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Availability switches 2 and 3 (doc 02 §5). is_86d_until is a BUSINESS DAY:
-- the 86 stands through that day and auto-clears when the branch's business
-- day moves past it.
CREATE TABLE branch_item_settings (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    item_id CHAR(36) NOT NULL,
    is_available TINYINT NOT NULL DEFAULT 1,
    is_86d TINYINT NOT NULL DEFAULT 0,
    is_86d_until DATE NULL,
    updated_at_utc DATETIME NOT NULL,
    updated_by_user_id CHAR(36) NOT NULL,
    CONSTRAINT fk_bis_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_bis_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT uq_branch_item_settings UNIQUE (branch_id, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE branch_option_settings (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    option_id CHAR(36) NOT NULL,
    is_86d TINYINT NOT NULL DEFAULT 0,
    is_86d_until DATE NULL,
    updated_at_utc DATETIME NOT NULL,
    updated_by_user_id CHAR(36) NOT NULL,
    CONSTRAINT fk_bos_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT fk_bos_option FOREIGN KEY (option_id) REFERENCES modifier_options (id),
    CONSTRAINT uq_branch_option_settings UNIQUE (branch_id, option_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recipes schema only — entry is optional until Phase 8 (doc 13 Phase 2).
-- ingredient_ref stays a soft reference until inventory items exist (Phase 8 FK).
CREATE TABLE recipe_lines (
    id CHAR(36) NOT NULL PRIMARY KEY,
    item_id CHAR(36) NOT NULL,
    ingredient_ref VARCHAR(120) NOT NULL,
    quantity INT NOT NULL,
    unit VARCHAR(10) NOT NULL,
    is_optional TINYINT NOT NULL DEFAULT 0,
    yield_portions INT NULL,
    effective_from DATE NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_recipe_lines_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT ck_recipe_unit CHECK (unit IN ('g', 'ml', 'piece'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FX rates (doc 10 §2): set centrally, optional branch override, versioned.
-- The rate is a decimal STRING — it must never pass through floating point.
CREATE TABLE fx_rates (
    id CHAR(36) NOT NULL PRIMARY KEY,
    base_currency CHAR(3) NOT NULL DEFAULT 'USD',
    quote_currency CHAR(3) NOT NULL DEFAULT 'IQD',
    rate VARCHAR(30) NOT NULL,
    branch_id CHAR(36) NULL,
    effective_from DATE NOT NULL,
    created_at_utc DATETIME NOT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    CONSTRAINT fk_fx_branch FOREIGN KEY (branch_id) REFERENCES branches (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Branch devices (doc 12 §9, doc 15): bearer of the pull/push token.
-- Registration here in Phase 2 (pull needs auth); hardening in Phase 4.
CREATE TABLE devices (
    id CHAR(36) NOT NULL PRIMARY KEY,
    branch_id CHAR(36) NOT NULL,
    name VARCHAR(100) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at_utc DATETIME NOT NULL,
    created_by_user_id CHAR(36) NOT NULL,
    revoked_at_utc DATETIME NULL,
    last_pull_at_utc DATETIME NULL,
    last_pull_cursor BIGINT NULL,
    CONSTRAINT fk_devices_branch FOREIGN KEY (branch_id) REFERENCES branches (id),
    CONSTRAINT ck_devices_status CHECK (status IN ('active', 'revoked'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Master-data change log driving /api/sync/pull (doc 15). Central-only
-- infrastructure: the AUTO_INCREMENT sequence is the pull cursor. This is not
-- a device-created domain record, so the doc 10 §1 UUID rule does not apply.
CREATE TABLE sync_changes (
    seq BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity VARCHAR(40) NOT NULL,
    entity_id CHAR(36) NOT NULL,
    op VARCHAR(12) NOT NULL DEFAULT 'upsert',
    changed_at_utc DATETIME NOT NULL,
    CONSTRAINT ck_sync_changes_op CHECK (op IN ('upsert', 'deactivate'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_sync_changes_entity ON sync_changes (entity, entity_id);
