-- ============================================================================
-- INSIGHTFUL SAGA SCHEMA EXPORT — SNOWFLAKE DDL
-- Generated: 2026-11-12T16:10:34.855Z
-- ============================================================================

CREATE TABLE IF NOT EXISTS "FACT_SALES_MONTHLY" (
  "SALES_ID"           NUMBER(38,0) NOT NULL,
  "DATE_KEY"           NUMBER(38,0),
  "STORE_ID"           NUMBER(38,0),
  "PRODUCT_ID"         NUMBER(38,0),
  "CUSTOMER_ID"        NUMBER(38,0),
  "QUANTITY_SOLD"      NUMBER(38,0),
  "GROSS_REVENUE"      DECIMAL(14,2),
  "DISCOUNT_AMOUNT"    DECIMAL(12,2),
  "NET_REVENUE"        DECIMAL(14,2),
  CONSTRAINT pk_fact_sales_monthly PRIMARY KEY ("SALES_ID")
);

COMMENT ON TABLE "FACT_SALES_MONTHLY" IS 'Monthly aggregated retail sales metrics and key performance indicators';

CREATE TABLE IF NOT EXISTS "DIM_STORE" (
  "STORE_ID"           NUMBER(38,0) NOT NULL,
  "STORE_NAME"         VARCHAR,
  "CITY_ID"            NUMBER(38,0),
  "SQUARE_FOOTAGE"     NUMBER(38,0),
  "OPEN_DATE"          DATE,
  CONSTRAINT pk_dim_store PRIMARY KEY ("STORE_ID")
);

COMMENT ON TABLE "DIM_STORE" IS 'Physical retail store locations and operational formats';

CREATE TABLE IF NOT EXISTS "DIM_PRODUCT" (
  "PRODUCT_ID"         NUMBER(38,0) NOT NULL,
  "SKU"                VARCHAR,
  "PRODUCT_TITLE"      VARCHAR,
  "SUBCATEGORY_ID"     NUMBER(38,0),
  "BRAND"              VARCHAR,
  CONSTRAINT pk_dim_product PRIMARY KEY ("PRODUCT_ID")
);

COMMENT ON TABLE "DIM_PRODUCT" IS 'Product merchandise master catalog';

CREATE TABLE IF NOT EXISTS "DIM_CITY" (
  "CITY_ID"            NUMBER(38,0) NOT NULL,
  "CITY_NAME"          VARCHAR,
  "STATE_PROVINCE"     VARCHAR,
  "REGION_ID"          NUMBER(38,0),
  CONSTRAINT pk_dim_city PRIMARY KEY ("CITY_ID")
);

COMMENT ON TABLE "DIM_CITY" IS 'Normalized municipal store geography';

CREATE TABLE IF NOT EXISTS "DIM_PRODUCT_SUBCATEGORY" (
  "SUBCATEGORY_ID"     NUMBER(38,0) NOT NULL,
  "SUBCATEGORY_NAME"   VARCHAR,
  "CATEGORY_ID"        NUMBER(38,0),
  CONSTRAINT pk_dim_product_subcategory PRIMARY KEY ("SUBCATEGORY_ID")
);

COMMENT ON TABLE "DIM_PRODUCT_SUBCATEGORY" IS 'Granular product taxonomy sub-categories';

CREATE TABLE IF NOT EXISTS "DIM_REGION_COUNTRY" (
  "REGION_ID"          NUMBER(38,0) NOT NULL,
  "REGION_NAME"        VARCHAR,
  "COUNTRY_NAME"       VARCHAR,
  "CURRENCY_CODE"      CHAR(3),
  CONSTRAINT pk_dim_region_country PRIMARY KEY ("REGION_ID")
);

COMMENT ON TABLE "DIM_REGION_COUNTRY" IS 'Macro global territory and currency jurisdiction';