bartender/migrations/migrations_v1.sql

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

CREATE TABLE "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 "status" (
    "status_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "status_status" TEXT NOT NULL
);

CREATE TABLE "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 "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 "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 "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 "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_finished_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 "order_item" (
    "order_item_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "order_id" INTEGER NOT NULL,
    "user_id" INTEGER NOT NULL,
    "product_id" INTEGER NOT NULL,
    "company_id" INTEGER NOT NULL,
    "order_item_flag" TEXT NOT NULL,
    "order_item_kitchen_note" TEXT NOT NULL DEFAULT '',
    FOREIGN KEY ("order_id") REFERENCES "order" ("order_id"),
    FOREIGN KEY ("user_id") REFERENCES "user" ("user_id"),
    FOREIGN KEY ("product_id") REFERENCES "product" ("product_id"),
    FOREIGN KEY ("company_id") REFERENCES "company" ("company_id")
);

CREATE TABLE "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 "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 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")
);

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