CREATE SCHEMA IF NOT EXISTS bazzar_pos;

CREATE TABLE IF NOT EXISTS bazzar_pos.roles (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE
);

INSERT INTO bazzar_pos.roles (code)
VALUES ('superadmin'), ('admin'), ('cashier')
ON CONFLICT (code) DO NOTHING;

CREATE TABLE IF NOT EXISTS bazzar_pos.users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role_id BIGINT NOT NULL REFERENCES bazzar_pos.roles(id),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.outlets (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS bazzar_pos.products (
    id BIGSERIAL PRIMARY KEY,
    item_code VARCHAR(80) NOT NULL UNIQUE,
    barcode VARCHAR(80),
    name VARCHAR(255) NOT NULL,
    brand VARCHAR(100),
    category VARCHAR(100),
    gender VARCHAR(40),
    function VARCHAR(100),
    size VARCHAR(40),
    collection VARCHAR(120),
    base_price NUMERIC(14,2) NOT NULL CHECK (base_price >= 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.product_prices (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    outlet_id BIGINT REFERENCES bazzar_pos.outlets(id),
    normal_price NUMERIC(14,2) NOT NULL CHECK (normal_price >= 0),
    promo_price NUMERIC(14,2) CHECK (promo_price >= 0),
    effective_from TIMESTAMPTZ NOT NULL,
    effective_to TIMESTAMPTZ,
    updated_by BIGINT REFERENCES bazzar_pos.users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.price_change_requests (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    requested_price NUMERIC(14,2) NOT NULL CHECK (requested_price >= 0),
    reason TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
    requested_by BIGINT NOT NULL REFERENCES bazzar_pos.users(id),
    approved_by BIGINT REFERENCES bazzar_pos.users(id),
    approved_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.members (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(40),
    email VARCHAR(150),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.sales_orders (
    id BIGSERIAL PRIMARY KEY,
    outlet_id BIGINT NOT NULL REFERENCES bazzar_pos.outlets(id),
    invoice_no VARCHAR(100) NOT NULL UNIQUE,
    member_id BIGINT REFERENCES bazzar_pos.members(id),
    cashier_id BIGINT NOT NULL REFERENCES bazzar_pos.users(id),
    subtotal NUMERIC(14,2) NOT NULL CHECK (subtotal >= 0),
    discount_total NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (discount_total >= 0),
    grand_total NUMERIC(14,2) NOT NULL CHECK (grand_total >= 0),
    status VARCHAR(30) NOT NULL,
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.sales_order_items (
    id BIGSERIAL PRIMARY KEY,
    sales_order_id BIGINT NOT NULL REFERENCES bazzar_pos.sales_orders(id),
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    qty INTEGER NOT NULL CHECK (qty > 0),
    unit_price NUMERIC(14,2) NOT NULL CHECK (unit_price >= 0),
    discount_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
    subtotal NUMERIC(14,2) NOT NULL CHECK (subtotal >= 0)
);

CREATE TABLE IF NOT EXISTS bazzar_pos.stock_movements (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    outlet_id BIGINT NOT NULL REFERENCES bazzar_pos.outlets(id),
    movement_type VARCHAR(20) NOT NULL CHECK (movement_type IN ('in', 'out', 'adjustment')),
    qty INTEGER NOT NULL,
    ref_type VARCHAR(50),
    ref_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.import_jobs (
    id BIGSERIAL PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL,
    type VARCHAR(80) NOT NULL,
    status VARCHAR(30) NOT NULL,
    total_rows INTEGER NOT NULL DEFAULT 0,
    success_rows INTEGER NOT NULL DEFAULT 0,
    failed_rows INTEGER NOT NULL DEFAULT 0,
    log_json JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.audit_logs (
    id BIGSERIAL PRIMARY KEY,
    actor_id BIGINT REFERENCES bazzar_pos.users(id),
    module VARCHAR(80) NOT NULL,
    action VARCHAR(80) NOT NULL,
    before_json JSONB,
    after_json JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

