bartender/migrations/migrations_v1.sql

CREATE TABLE IF NOT EXISTS "company" (
    "company_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_name" TEXT NOT NULL,
    "company_flag" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "role" (
    "role_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "role_name" TEXT NOT NULL,
    "role_permission" TEXT NOT NULL,
    "role_flag" TEXT NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "status" (
    "status_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "status_status" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS "description" (
  description_id INTEGER PRIMARY KEY AUTOINCREMENT,
  description_text TEXT NOT NULL,
  product_id INTEGER NOT NULL,
  company_id INTEGER NOT NULL,
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE IF NOT EXISTS "user" (
    "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_name" TEXT NOT NULL,
    "user_email" TEXT NOT NULL UNIQUE,
    "user_password" TEXT NOT NULL,
    "company_id" INTEGER NOT NULL,
    "role_id" INTEGER NOT NULL,
    "user_flag" TEXT NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id"),
    FOREIGN KEY ("role_id") REFERENCES "role" ("role_id")
);

CREATE TABLE IF NOT EXISTS "table" (
    "table_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "table_number" TEXT NOT NULL,
    "status_id" INTEGER NOT NULL,
    "table_flag" TEXT NOT NULL,
    FOREIGN KEY ("status_id") REFERENCES "status" ("status_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "category" (
    "category_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "category_name" TEXT NOT NULL,
    "category_flag" TEXT NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "product" (
    "product_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "category_id" INTEGER NOT NULL,
    "product_is_kitchen" BOOLEAN NOT NULL,
    "product_name" TEXT NOT NULL,
    "product_price" TEXT NOT NULL,
    "product_flag" TEXT NOT NULL,    
    FOREIGN KEY ("category_id") REFERENCES "category" ("category_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "order" (
    "order_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "table_id" INTEGER NOT NULL,
    "user_id" INTEGER NOT NULL,
    "company_id" INTEGER NOT NULL,
    "order_name" TEXT NOT NULL,
    "order_phone" TEXT NOT NULL,
    "status_id" INTEGER NOT NULL,
    "order_created_at" TEXT NOT NULL,
    "order_flag" TEXT NOT NULL,
    FOREIGN KEY ("table_id") REFERENCES "table" ("table_id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("user_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id"),
    FOREIGN KEY ("status_id") REFERENCES "status" ("status_id")
);

CREATE TABLE IF NOT EXISTS "order_item" (
    "order_item_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "order_id" INTEGER NOT NULL,
    "product_id" INTEGER NOT NULL,
    "company_id" INTEGER NOT NULL,
    FOREIGN KEY ("order_id") REFERENCES "order" ("order_id"),
    FOREIGN KEY ("product_id") REFERENCES "product" ("product_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "sale" (
    "sale_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "order_id" INTEGER NOT NULL,
    "product_id" INTEGER NOT NULL,
    "sale_total" TEXT NOT NULL,
    "sale_created_at" TEXT NOT NULL,
    "sale_flag" TEXT NOT NULL,
    FOREIGN KEY ("order_id") REFERENCES "order" ("order_id"),
    FOREIGN KEY ("product_id") REFERENCES "product" ("product_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "fee" (
    "fee_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "company_id" INTEGER NOT NULL,
    "fee_fixed" TEXT NOT NULL,
    "fee_perc" TEXT NOT NULL,
    "fee_flag" TEXT NOT NULL,
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE IF NOT EXISTS "api_key" (
    "api_key_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL,
    "api_key_user" TEXT NOT NULL,
    "api_key_secret" TEXT NOT NULL,
    FOREIGN KEY ("user_id") REFERENCES "user" ("user_id")
);