model commerce {
  version "1.0"
  description "Online shop: customers, products, orders."
}

naming {
  entity {
    case snake
  }
  column {
    case snake
    max_length 63
  }
  foreign_key {
    prefix "fk_"
  }
  index {
    prefix "ix_"
  }
}

lint {
  missing-primary-key error
  unindexed-foreign-key warn
}

domain email {
  type text(320)
  not null
  check "value ~ '^[^@]+@[^@]+$'"
  description "RFC 5321 address, case preserved."
}

domain money {
  type decimal(12, 2)
  not null
  default 0
}

enum order_status {
  value "pending"
  value "paid"
  value "shipped"
  value "cancelled"
}

term Customer {
  definition "A person or company that has placed or may place orders."
}

term Order {
  definition "A customer's request to buy one or more products."
  alias "Purchase"
}

entity address {
  description "A postal address of a customer."
  attribute id : bigint not null
  attribute customer_id : bigint not null
  attribute line1 : text(200) not null
  attribute line2 : text(200)
  attribute city : text(100) not null
  attribute postal_code : text(20) not null
  attribute country : char(2) not null
  key primary pk_address (id)
}

entity category {
  description "A product category; categories form a tree."
  attribute id : bigint not null
  attribute parent_id : bigint
  attribute name : text(100) not null
  key primary pk_category (id)
  key unique uq_category_name (name)
}

entity coupon {
  attribute id : bigint not null
  attribute code : text(40) not null
  attribute percent_off : smallint not null
  attribute valid_until : date
  key primary pk_coupon (id)
  key unique uq_coupon_code (code)
}

entity customer {
  description "A registered buyer."
  term Customer
  attribute id : bigint not null
  attribute email : domain email
  attribute full_name : text(200) not null
  attribute created_at : timestamptz not null default expr "now()"
  key primary pk_customer (id)
  key unique uq_customer_email (email)
}

entity order {
  description "A placed order."
  term Order
  attribute id : bigint not null
  attribute customer_id : bigint not null
  attribute coupon_id : bigint
  attribute status : enum order_status not null default "pending"
  attribute total : domain money
  attribute placed_at : timestamptz not null default expr "now()"
  key primary pk_order (id)
}

entity order_line {
  attribute order_id : bigint not null
  attribute line_no : integer not null
  attribute product_id : bigint not null
  attribute qty : integer not null default 1
  attribute unit_price : domain money
  key primary pk_order_line (order_id, line_no)
}

entity payment {
  attribute id : bigint not null
  attribute order_id : bigint not null
  attribute reference_no : bigint not null
  attribute amount : domain money
  attribute paid_at : timestamptz not null default expr "now()"
  key primary pk_payment (id)
  key unique uq_payment_reference (reference_no)
}

entity product {
  description "Something for sale."
  attribute id : bigint not null
  attribute category_id : bigint not null
  attribute sku : text(40) not null
  attribute name : text(200) not null
  attribute price : domain money
  attribute attributes : json
  attribute active : boolean not null default true
  key primary pk_product (id)
  key unique uq_product_sku (sku)
}

relationship address_customer {
  parent customer
  child address
  columns (customer_id -> id)
  cardinality one to many
  on delete cascade
}

relationship category_parent {
  description "A category may have a parent category."
  parent category
  child category
  columns (parent_id -> id)
  cardinality zero_or_one to many
}

relationship order_coupon {
  parent coupon
  child order
  columns (coupon_id -> id)
  cardinality zero_or_one to many
  on delete set_null
}

relationship order_customer {
  parent customer
  child order
  columns (customer_id -> id)
  cardinality one to many
  on delete restrict
}

relationship order_line_order {
  parent order
  child order_line
  columns (order_id -> id)
  cardinality one to one_or_many
  identifying
  on delete cascade
}

relationship order_line_product {
  parent product
  child order_line
  columns (product_id -> id)
  cardinality one to many
}

relationship payment_order {
  parent order
  child payment
  columns (order_id -> id)
  cardinality one to many
}

relationship product_category {
  parent category
  child product
  columns (category_id -> id)
  cardinality one to many
}

area Catalog {
  description "Products and categories."
  entity category
  entity product
}

area Sales {
  description "Ordering flow."
  notation idef1x
  entity address at 40 300
  entity coupon at 680 40
  entity customer at 40 40
  entity order at 360 40
  entity order_line at 360 300
  entity payment at 680 300
}

target lite : sqlite {
  schema main {
    enum_type order_status ("pending", "paid", "shipped", "cancelled")
    table address {
      entity address
      column id integer not null identity by_default attribute id
      column customer_id integer not null attribute customer_id
      column line1 text not null attribute line1
      column line2 text attribute line2
      column city text not null attribute city
      column postal_code text not null attribute postal_code
      column country text not null attribute country
      primary key pk_address (id)
      foreign key fk_address_customer (customer_id) references customer (id) on delete cascade relationship address_customer
      index ix_address_customer (customer_id)
    }
    table category {
      entity category
      column id integer not null identity by_default attribute id
      column parent_id integer attribute parent_id
      column name text not null attribute name
      primary key pk_category (id)
      unique uq_category_name (name)
      foreign key fk_category_parent (parent_id) references category (id) relationship category_parent
      index ix_category_parent (parent_id)
    }
    table coupon {
      entity coupon
      column id integer not null identity by_default attribute id
      column code text not null attribute code
      column percent_off integer not null attribute percent_off
      column valid_until text attribute valid_until
      primary key pk_coupon (id)
      unique uq_coupon_code (code)
    }
    table customer {
      entity customer
      column id integer not null identity by_default attribute id
      column email text not null attribute email
      column full_name text not null attribute full_name
      column created_at text not null default expr "CURRENT_TIMESTAMP" attribute created_at
      primary key pk_customer (id)
      unique uq_customer_email (email)
      check ck_customer_email "email LIKE '%_@_%'"
    }
    table order {
      entity order
      column id integer not null identity by_default attribute id
      column customer_id integer not null attribute customer_id
      column coupon_id integer attribute coupon_id
      column status order_status not null default "pending" attribute status
      column total numeric not null default 0 attribute total
      column placed_at text not null default expr "CURRENT_TIMESTAMP" attribute placed_at
      primary key pk_order (id)
      foreign key fk_order_coupon (coupon_id) references coupon (id) on delete set_null relationship order_coupon
      foreign key fk_order_customer (customer_id) references customer (id) on delete restrict relationship order_customer
      index ix_order_coupon (coupon_id)
      index ix_order_customer (customer_id)
    }
    table order_line {
      entity order_line
      column order_id integer not null attribute order_id
      column line_no integer not null attribute line_no
      column product_id integer not null attribute product_id
      column qty integer not null default 1 attribute qty
      column unit_price numeric not null default 0 attribute unit_price
      primary key pk_order_line (order_id, line_no)
      foreign key fk_order_line_order (order_id) references order (id) on delete cascade relationship order_line_order
      foreign key fk_order_line_product (product_id) references product (id) relationship order_line_product
      index ix_order_line_product (product_id)
    }
    table payment {
      entity payment
      column id integer not null identity by_default attribute id
      column order_id integer not null attribute order_id
      column reference_no integer not null attribute reference_no
      column amount numeric not null default 0 attribute amount
      column paid_at text not null default expr "CURRENT_TIMESTAMP" attribute paid_at
      primary key pk_payment (id)
      unique uq_payment_reference (reference_no)
      foreign key fk_payment_order (order_id) references order (id) relationship payment_order
      index ix_payment_order (order_id)
    }
    table product {
      entity product
      column id integer not null identity by_default attribute id
      column category_id integer not null attribute category_id
      column sku text not null attribute sku
      column name text not null attribute name
      column price numeric not null default 0 attribute price
      column attributes text attribute attributes
      column active integer not null default 1 attribute active
      primary key pk_product (id)
      unique uq_product_sku (sku)
      foreign key fk_product_category (category_id) references category (id) relationship product_category
      index ix_product_category (category_id)
    }
  }
}

target pg : postgresql {
  role app_rw {
    login
  }
  schema public {
    enum_type order_status ("pending", "paid", "shipped", "cancelled")
    sequence payment_reference_seq {
      start 1000
      increment 1
    }
    table address {
      entity address
      comment "A postal address of a customer."
      column id bigint not null identity always attribute id
      column customer_id bigint not null attribute customer_id
      column line1 varchar(200) not null attribute line1
      column line2 varchar(200) attribute line2
      column city varchar(100) not null attribute city
      column postal_code varchar(20) not null attribute postal_code
      column country char(2) not null attribute country
      primary key pk_address (id)
      foreign key fk_address_customer (customer_id) references customer (id) on delete cascade relationship address_customer
      index ix_address_customer (customer_id)
    }
    table category {
      entity category
      comment "A product category; categories form a tree."
      column id bigint not null identity always attribute id
      column parent_id bigint attribute parent_id
      column name varchar(100) not null attribute name
      primary key pk_category (id)
      unique uq_category_name (name)
      foreign key fk_category_parent (parent_id) references category (id) relationship category_parent
      index ix_category_parent (parent_id)
    }
    table coupon {
      entity coupon
      column id bigint not null identity always attribute id
      column code varchar(40) not null attribute code
      column percent_off smallint not null attribute percent_off
      column valid_until date attribute valid_until
      primary key pk_coupon (id)
      unique uq_coupon_code (code)
    }
    table customer {
      entity customer
      comment "A registered buyer."
      column id bigint not null identity always attribute id
      column email varchar(320) not null attribute email
      column full_name varchar(200) not null attribute full_name
      column created_at "timestamp with time zone" not null default expr "now()" attribute created_at
      primary key pk_customer (id)
      unique uq_customer_email (email)
      check ck_customer_email "email ~ '^[^@]+@[^@]+$'"
    }
    table order {
      entity order
      comment "A placed order."
      column id bigint not null identity always attribute id
      column customer_id bigint not null attribute customer_id
      column coupon_id bigint attribute coupon_id
      column status order_status not null default "pending" attribute status
      column total numeric(12, 2) not null default 0 attribute total
      column placed_at "timestamp with time zone" not null default expr "now()" attribute placed_at
      primary key pk_order (id)
      foreign key fk_order_coupon (coupon_id) references coupon (id) on delete set_null relationship order_coupon
      foreign key fk_order_customer (customer_id) references customer (id) on delete restrict relationship order_customer
      index ix_order_coupon (coupon_id)
      index ix_order_customer (customer_id)
      trigger trg_order_touch {
        timing before
        events update
        function set_updated_at
      }
    }
    table order_line {
      entity order_line
      column order_id bigint not null attribute order_id
      column line_no integer not null attribute line_no
      column product_id bigint not null attribute product_id
      column qty integer not null default 1 attribute qty
      column unit_price numeric(12, 2) not null default 0 attribute unit_price
      primary key pk_order_line (order_id, line_no)
      foreign key fk_order_line_order (order_id) references order (id) on delete cascade relationship order_line_order
      foreign key fk_order_line_product (product_id) references product (id) relationship order_line_product
      index ix_order_line_product (product_id)
    }
    table payment {
      entity payment
      column id bigint not null identity always attribute id
      column order_id bigint not null attribute order_id
      column reference_no bigint not null default expr "nextval('payment_reference_seq')" attribute reference_no
      column amount numeric(12, 2) not null default 0 attribute amount
      column paid_at "timestamp with time zone" not null default expr "now()" attribute paid_at
      primary key pk_payment (id)
      unique uq_payment_reference (reference_no)
      foreign key fk_payment_order (order_id) references order (id) relationship payment_order
      index ix_payment_order (order_id)
    }
    table product {
      entity product
      comment "Something for sale."
      column id bigint not null identity always attribute id
      column category_id bigint not null attribute category_id
      column sku varchar(40) not null attribute sku
      column name varchar(200) not null attribute name
      column price numeric(12, 2) not null default 0 attribute price
      column attributes jsonb attribute attributes
      column active boolean not null default true attribute active
      primary key pk_product (id)
      unique uq_product_sku (sku)
      foreign key fk_product_category (category_id) references category (id) relationship product_category
      index ix_product_category (category_id)
    }
    view v_order_totals {
      comment "Order totals computed from lines."
      sql """
SELECT o.id AS order_id, SUM(l.qty * l.unit_price) AS total
FROM public."order" o
JOIN public.order_line l ON l.order_id = o.id
GROUP BY o.id
"""
    }
    materialized_view mv_daily_sales {
      sql """
SELECT date_trunc('day', placed_at) AS day, sum(total) AS total FROM public."order" GROUP BY 1
"""
      index ix_mv_daily_sales_day unique (day)
    }
    function set_updated_at() {
      returns "trigger"
      language plpgsql
      body """
BEGIN
  RETURN NEW;
END;
"""
    }
    grant select, insert, update on table customer to app_rw
    grant select on all tables to app_rw
  }
}