DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;

SET search_path TO project;

-- =====================================================
-- 1. ROLE
-- =====================================================
CREATE TABLE role (
    role_id     SERIAL PRIMARY KEY,
    role_name   VARCHAR(50) NOT NULL,
    description TEXT,
    CONSTRAINT unique_role_name UNIQUE (role_name)
);

-- =====================================================
-- 2. APP_USER
-- =====================================================
CREATE TABLE app_user (
    user_id       SERIAL PRIMARY KEY,
    role_id       INTEGER NOT NULL,
    username      VARCHAR(50),
    password_hash TEXT NOT NULL,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    active        BOOLEAN DEFAULT TRUE,
    CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES role(role_id),
    CONSTRAINT app_user_username_key UNIQUE (username)
);

-- =====================================================
-- 3. SESSION
-- =====================================================
CREATE TABLE session (
    token      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    expires_at TIMESTAMP NOT NULL DEFAULT (now() + '12:00:00'::interval),
    CONSTRAINT session_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
);

-- =====================================================
-- 4. AUDIT_LOG
-- =====================================================
CREATE TABLE audit_log (
    log_id      SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    action      TEXT NOT NULL,
    entity_name VARCHAR(50),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT audit_log_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
);

-- =====================================================
-- 5. SHIFT
-- =====================================================
CREATE TABLE shift (
    shift_id   SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time   TIMESTAMP,
    CONSTRAINT shift_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id)
);

-- =====================================================
-- 6. SHIFT_CLOSE
-- =====================================================
CREATE TABLE shift_close (
    shift_id    SERIAL PRIMARY KEY,
    closed_at   TIMESTAMP NOT NULL DEFAULT now(),
    closed_by   INTEGER,
    total       NUMERIC NOT NULL,
    order_count INTEGER NOT NULL,
    CONSTRAINT shift_close_closed_by_fkey FOREIGN KEY (closed_by) REFERENCES app_user(user_id)
);

-- =====================================================
-- 7. CATEGORY
-- =====================================================
CREATE TABLE category (
    category_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    active      BOOLEAN DEFAULT TRUE
);

-- =====================================================
-- 8. PRODUCT
-- =====================================================
CREATE TABLE product (
    product_id   SERIAL PRIMARY KEY,
    category_id  INTEGER NOT NULL,
    name         VARCHAR(100) NOT NULL,
    description  TEXT,
    price        NUMERIC NOT NULL,
    active       BOOLEAN DEFAULT TRUE,
    min_stock    INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category(category_id)
);

-- =====================================================
-- 9. RECIPE
-- =====================================================
CREATE TABLE recipe (
    recipe_id  SERIAL PRIMARY KEY,
    product_id INTEGER,
    CONSTRAINT recipe_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id),
    CONSTRAINT recipe_product_id_key UNIQUE (product_id)
);

-- =====================================================
-- 10. RECIPE_ITEM
-- =====================================================
CREATE TABLE recipe_item (
    recipe_id       INTEGER NOT NULL,
    ingredient_id   INTEGER NOT NULL,
    quantity_needed NUMERIC NOT NULL,
    CONSTRAINT recipe_item_pkey PRIMARY KEY (recipe_id, ingredient_id),
    CONSTRAINT recipe_item_recipe_id_fkey FOREIGN KEY (recipe_id) REFERENCES recipe(recipe_id),
    CONSTRAINT recipe_item_ingredient_id_fkey FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
);

-- =====================================================
-- 11. INGREDIENT
-- =====================================================
CREATE TABLE ingredient (
    ingredient_id SERIAL PRIMARY KEY,
    name          VARCHAR(200) NOT NULL,
    unit          VARCHAR(20) NOT NULL,
    min_stock     NUMERIC NOT NULL DEFAULT 0,
    active        BOOLEAN NOT NULL DEFAULT TRUE
);

-- =====================================================
-- 12. INGREDIENT_INVENTORY
-- =====================================================
CREATE TABLE ingredient_inventory (
    id              SERIAL PRIMARY KEY,
    ingredient_id   INTEGER NOT NULL,
    quantity_change NUMERIC NOT NULL,
    operation_type  VARCHAR(40) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    CONSTRAINT ingredient_inventory_ingredient_id_fkey FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
);

-- =====================================================
-- 13. INVENTORY
-- =====================================================
CREATE TABLE inventory (
    inventory_id    SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL,
    quantity_change INTEGER NOT NULL,
    operation_type  VARCHAR(30),
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT inventory_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- =====================================================
-- 14. RESTAURANT_TABLE
-- =====================================================
CREATE TABLE restaurant_table (
    table_id     SERIAL PRIMARY KEY,
    table_number VARCHAR(20) NOT NULL,
    capacity     INTEGER NOT NULL,
    status       VARCHAR(20) NOT NULL,
    CONSTRAINT restaurant_table_table_number_key UNIQUE (table_number)
);

-- =====================================================
-- 15. ORDERS
-- =====================================================
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    table_id   INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    status     VARCHAR(20) NOT NULL,
    CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES app_user(user_id),
    CONSTRAINT orders_table_id_fkey FOREIGN KEY (table_id) REFERENCES restaurant_table(table_id)
);

-- =====================================================
-- 16. ORDER_ITEM
-- =====================================================
CREATE TABLE order_item (
    order_id   INTEGER NOT NULL,
    item_number INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL,
    unit_price NUMERIC NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'ПОРАЧАНО',
    CONSTRAINT order_item_pkey PRIMARY KEY (order_id, item_number),
    CONSTRAINT order_item_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT order_item_product_id_fkey FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- =====================================================
-- 17. PAYMENT
-- =====================================================
CREATE TABLE payment (
    payment_id   SERIAL PRIMARY KEY,
    order_id     INTEGER NOT NULL,
    amount       NUMERIC NOT NULL,
    method       VARCHAR(20) NOT NULL,
    payment_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT payment_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT payment_order_id_key UNIQUE (order_id)
);

-- =====================================================
-- 18. INVOICE
-- =====================================================
CREATE TABLE invoice (
    invoice_id     SERIAL PRIMARY KEY,
    payment_id     INTEGER NOT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    issued_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT invoice_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES payment(payment_id),
    CONSTRAINT invoice_payment_id_key UNIQUE (payment_id),
    CONSTRAINT invoice_invoice_number_key UNIQUE (invoice_number)
);

-- =====================================================
-- 19. APP_SETTINGS
-- =====================================================
CREATE TABLE app_settings (
    id              INTEGER PRIMARY KEY DEFAULT 1,
    restaurant_name VARCHAR(100) NOT NULL DEFAULT 'Мој Ресторан',
    vat_rate        NUMERIC NOT NULL DEFAULT 18.00,
    currency        VARCHAR(10) NOT NULL DEFAULT 'ден',
    printer_config  VARCHAR(255) NOT NULL DEFAULT 'default'
);

-- =====================================================
-- 20. SETTINGS
-- =====================================================
CREATE TABLE settings (
    id              INTEGER PRIMARY KEY DEFAULT 1,
    restaurant_name VARCHAR(200) NOT NULL DEFAULT 'EMPORIO CAFFE',
    vat_percent     NUMERIC NOT NULL DEFAULT 18,
    currency        VARCHAR(10) NOT NULL DEFAULT 'ден',
    printer_ip      VARCHAR(64),
    printer_port    INTEGER NOT NULL DEFAULT 9100
);