CREATE TABLE users(
    "id" uuid default gen_random_uuid() primary key not null,
    "index_id" bigserial not null,
    "email" varchar unique not null,
    "password" varchar not null,
    "fullname" varchar not null,
    "created_at" timestamp default(now()),
    "updated_at" timestamp default(now())
);

CREATE TABLE product_types(
  "id" smallserial primary key not null,
  "name" varchar(35) not null
);

INSERT INTO product_types 
VALUES 
( 1, 'Barang Jadi'),
( 2, 'Bahan Baku');

CREATE TABLE merchants (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "name" varchar not null,
  "owner_id" uuid references "users"("id") not null,
  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

create table suppliers (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "merchant_id" uuid references "merchants"("id") not null,
  "name" varchar(100) not null,
  "detail" jsonb,

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE customers (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "merchant_id" uuid references "merchants"("id") not null,
  "name" varchar not null,
  "detail" jsonb[],

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE products (
  "id" uuid default gen_random_uuid() primary key not null,
  "merchant_id" uuid references "merchants"("id") not null,
  "product_type_id" smallint references "product_types"("id") default(1) not null,
  "index_id" bigserial not null,
  "name" varchar not null,
  "selling_price" double precision default(0::double precision) NOT NULL,
  "purchase_price" double precision default(0:: double precision) NOT NULL,
  "stock" double precision default(0::double precision) NOT NULL CHECK("stock" >= 0),
  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE purchase_order (
  "id" uuid default gen_random_uuid() primary key not null,
  "supplier_id" uuid references "suppliers"("id") not null,
  "merchant_id" uuid references "merchants"("id") not null,
  "index_id" bigserial not null,
  "created_by" uuid references "users"("id") not null,
  "code" varchar(100),
  "is_paid" boolean not null,
  "total" double precision not null,
  "paid_nominal" double precision not null,
  "note" text,

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE purchase_order_detail (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "code" text,
  "merchant_id" uuid references "merchants"("id") not null,
  "purchase_order_id" uuid references "purchase_order"("id") not null,
  "product_id" uuid references "products"("id") not null,
  "quantity" double precision not null,
  "sub_total" double precision not null,
  "product_price" double precision not null,

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);
CREATE TABLE sale_order (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "code" text,
  "created_by" uuid references "users"("id") not null,
  "merchant_id" uuid references "merchants"("id") not null,
  "customer_id" uuid references "customers"("id"),
  "is_paid" boolean,
  "total" double precision not null,
  "paid_nominal" double precision not null,
  "note" text,

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE sale_order_detail (
  "id" uuid default gen_random_uuid() primary key not null,
  "index_id" bigserial not null,
  "sale_order_id" uuid references "sale_order"("id") not null,
  "product_id" uuid references "products"("id") not null,
  "product_name" varchar not null,
  "quantity" double precision not null,
  "sub_total" double precision not null,
  "product_price" double precision not null,
  "profit" double precision not null,

  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE INDEX ON "users"("index_id");

CREATE INDEX ON "merchants"("index_id");

CREATE INDEX ON "suppliers"("index_id");

CREATE INDEX ON "customers"("index_id");

CREATE INDEX ON "products" ("name");
CREATE INDEX ON "products" ("selling_price");
CREATE INDEX ON "products" ("index_id");
CREATE INDEX ON "products" ("purchase_price");
CREATE INDEX ON "products" ("stock");

CREATE INDEX ON "purchase_order" ("merchant_id");
CREATE INDEX ON "purchase_order" ("supplier_id");
CREATE INDEX ON "purchase_order" ("index_id");
CREATE INDEX ON "purchase_order" ("created_at");

CREATE INDEX ON "purchase_order_detail" ("index_id");

CREATE INDEX ON "sale_order" ("index_id");

CREATE INDEX ON "sale_order_detail" ("index_id");