schema.sql

-- ============================================================================
-- FMCG Beverages — Text-to-SQL Agent Schema
-- Run this in the Supabase SQL editor (or via psql) before loading the CSVs.
-- ============================================================================

drop table if exists inventory;
drop table if exists sales_promotions;
drop table if exists product_master;
drop table if exists store_master;

create table product_master (
    product_id      text primary key,
    product_name    text not null,
    brand           text not null,
    category        text not null,
    sub_category    text not null,
    pack_size_ml    integer not null,
    unit_price      numeric(10, 2) not null
);

create table store_master (
    store_id        text primary key,
    store_name      text not null,
    region          text not null,
    city            text not null,
    store_format    text not null
);

create table sales_promotions (
    week_start_date  date not null,
    product_id       text not null references product_master(product_id),
    store_id         text not null references store_master(store_id),
    region           text not null,
    units_sold       integer not null,
    revenue          numeric(12, 2) not null,
    promotion_flag   boolean not null default false,
    promotion_type   text,
    discount_pct     numeric(4, 2) not null default 0,
    primary key (week_start_date, product_id, store_id)
);

create table inventory (
    week_start_date  date not null,
    product_id       text not null references product_master(product_id),
    store_id         text not null references store_master(store_id),
    opening_stock    integer not null,
    units_received   integer not null,
    units_sold       integer not null,
    closing_stock    integer not null,
    stockout_flag    boolean not null default false,
    primary key (week_start_date, product_id, store_id)
);

-- Indexes that matter for the queries the agent will actually run
create index idx_sales_product on sales_promotions(product_id);
create index idx_sales_store on sales_promotions(store_id);
create index idx_sales_region on sales_promotions(region);
create index idx_sales_week on sales_promotions(week_start_date);
create index idx_sales_promo on sales_promotions(promotion_flag);

create index idx_inv_product on inventory(product_id);
create index idx_inv_store on inventory(store_id);
create index idx_inv_week on inventory(week_start_date);
create index idx_inv_stockout on inventory(stockout_flag);

-- ----------------------------------------------------------------------------
-- IMPORTANT (this is the bug from the assessment writeup):
-- sales_promotions and inventory share the same (week_start_date, product_id,
-- store_id) key, but a row can exist in one table's *logical* business sense
-- without being meaningful in a naive INNER JOIN context (e.g. a fully
-- stocked-out product). Always default to LEFT JOIN from sales_promotions
-- to inventory (or vice versa, depending on the question) and verify row
-- counts against each source table independently before trusting an
-- aggregate built on the join. This constraint is enforced in the agent's
-- prompt (see src/prompts.py) and double-checked in the validator
-- (see src/validator.py).
-- ----------------------------------------------------------------------------