-- =====================================================================
--  DISTRO PIĆA — Šema baze podataka (MySQL 5.7+ / MariaDB)
--  Veleprodajna distribucija pića za kafiće, barove i restorane
--  Kodiranje: utf8mb4 (podrška za bosanska slova č ć đ š ž)
-- =====================================================================

CREATE DATABASE IF NOT EXISTS distro_pica
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE distro_pica;

-- ---------------------------------------------------------------------
-- 1. KORISNICI — svi tipovi korisnika u jednoj tabeli (role-based)
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role          ENUM('admin','staff','driver','client') NOT NULL,
    username      VARCHAR(50)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,              -- password_hash() / PASSWORD_DEFAULT
    full_name     VARCHAR(100) NOT NULL,
    phone         VARCHAR(30)  DEFAULT NULL,
    email         VARCHAR(120) DEFAULT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    last_login_at DATETIME     DEFAULT NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. KLIJENTI — poslovni podaci objekta (kafić / restoran / bar)
--    Vezani 1:1 na users tabelu (role = 'client')
-- ---------------------------------------------------------------------
CREATE TABLE clients (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id        INT UNSIGNED NOT NULL UNIQUE,
    business_name  VARCHAR(120) NOT NULL,             -- npr. "Caffe Bar Centar"
    business_type  ENUM('kafic','restoran','bar','hotel','ostalo') DEFAULT 'kafic',
    address        VARCHAR(200) NOT NULL,
    city           VARCHAR(80)  NOT NULL,
    id_number      VARCHAR(30)  DEFAULT NULL,         -- ID / PDV broj
    contact_person VARCHAR(100) DEFAULT NULL,
    notes          TEXT         DEFAULT NULL,
    lat            DECIMAL(10,7) DEFAULT NULL,        -- za buduće rute
    lng            DECIMAL(10,7) DEFAULT NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. KATEGORIJE PROIZVODA
-- ---------------------------------------------------------------------
CREATE TABLE categories (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80) NOT NULL,                  -- Gazirani sokovi, Pivo, Voda...
    sort_order INT NOT NULL DEFAULT 0,
    is_active  TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. PROIZVODI — srce sistema "Komad vs. Gajba"
--    price_piece  = cijena jednog komada
--    price_crate  = cijena cijele gajbe (može biti povoljnija od N × komad)
--    units_per_crate = koliko komada stane u gajbu
-- ---------------------------------------------------------------------
CREATE TABLE products (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id     INT UNSIGNED NOT NULL,
    sku             VARCHAR(40)  DEFAULT NULL UNIQUE,
    name            VARCHAR(120) NOT NULL,            -- "Coca-Cola 0.25L staklo"
    description     VARCHAR(255) DEFAULT NULL,
    volume_label    VARCHAR(30)  DEFAULT NULL,        -- "0.25L", "0.5L", "50L bure"
    units_per_crate SMALLINT UNSIGNED NOT NULL DEFAULT 24,
    price_piece     DECIMAL(10,2) NOT NULL,           -- KM po komadu
    price_crate     DECIMAL(10,2) NOT NULL,           -- KM po gajbi
    stock_pieces    INT NOT NULL DEFAULT 0,           -- zaliha se vodi u komadima
    image_url       VARCHAR(255) DEFAULT NULL,
    allow_piece     TINYINT(1) NOT NULL DEFAULT 1,    -- da li se smije naručiti komad
    allow_crate     TINYINT(1) NOT NULL DEFAULT 1,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_active_cat (is_active, category_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. NARUDŽBE — glavni workflow dokument
--    Statusi: pending → approved → dispatched → delivered  (ili cancelled)
-- ---------------------------------------------------------------------
CREATE TABLE orders (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number   VARCHAR(20) NOT NULL UNIQUE,       -- npr. "N-2026-00341"
    client_id      INT UNSIGNED NOT NULL,
    status         ENUM('pending','approved','dispatched','delivered','cancelled')
                   NOT NULL DEFAULT 'pending',
    total_amount   DECIMAL(12,2) NOT NULL DEFAULT 0,  -- KM, snapshot u trenutku narudžbe
    client_note    VARCHAR(500) DEFAULT NULL,         -- napomena klijenta
    staff_note     VARCHAR(500) DEFAULT NULL,         -- interna napomena dispečera
    approved_by    INT UNSIGNED DEFAULT NULL,         -- users.id (staff/admin)
    approved_at    DATETIME DEFAULT NULL,
    driver_id      INT UNSIGNED DEFAULT NULL,         -- users.id (driver)
    delivery_date  DATE DEFAULT NULL,                 -- planirani dan isporuke
    delivered_at   DATETIME DEFAULT NULL,
    delivery_note  VARCHAR(500) DEFAULT NULL,         -- napomena vozača kod isporuke
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id)   REFERENCES clients(id),
    FOREIGN KEY (approved_by) REFERENCES users(id),
    FOREIGN KEY (driver_id)   REFERENCES users(id),
    INDEX idx_status_date (status, created_at),
    INDEX idx_driver_day  (driver_id, delivery_date)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. STAVKE NARUDŽBE — pamti se JEDINICA (komad ili gajba) + snapshot cijene
--    total_pieces omogućava tovarni list (loading sheet) bez računanja u PHP-u
-- ---------------------------------------------------------------------
CREATE TABLE order_items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id        INT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED NOT NULL,
    unit_type       ENUM('piece','crate') NOT NULL,
    quantity        INT UNSIGNED NOT NULL,            -- broj komada ILI broj gajbi
    units_per_crate SMALLINT UNSIGNED NOT NULL,       -- snapshot (ako se kasnije mijenja)
    unit_price      DECIMAL(10,2) NOT NULL,           -- snapshot cijene jedinice
    line_total      DECIMAL(12,2) NOT NULL,           -- quantity × unit_price
    total_pieces    INT UNSIGNED NOT NULL,            -- quantity × (1 ili units_per_crate)
    FOREIGN KEY (order_id)   REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order (order_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. AKTIVNOSTI — audit log za Admin dashboard ("ko šta radi")
-- ---------------------------------------------------------------------
CREATE TABLE activity_log (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED DEFAULT NULL,
    action     VARCHAR(60) NOT NULL,     -- 'order.created', 'order.approved', 'login'...
    entity     VARCHAR(40) DEFAULT NULL, -- 'order', 'product', 'user'
    entity_id  INT UNSIGNED DEFAULT NULL,
    details    VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_time (created_at)
) ENGINE=InnoDB;

-- =====================================================================
--  DEMO PODACI
-- =====================================================================

-- Lozinka za sve demo naloge: "demo1234"
-- Hash generisan sa password_hash('demo1234', PASSWORD_DEFAULT)
SET @pw = '$2y$10$tr8r3I6NbPghA1OoIjUZ4O9NgeLIr0y9qnE1hRrRFWq4Vi3GlsxuK';

INSERT INTO users (role, username, password_hash, full_name, phone) VALUES
('admin',  'admin',    @pw, 'Emir Vlasnik',      '061-111-111'),
('staff',  'dispecer', @pw, 'Amila Dispečer',    '061-222-222'),
('driver', 'vozac1',   @pw, 'Haris Vozač',       '061-333-333'),
('client', 'kafana1',  @pw, 'Adnan Kafedžija',   '061-444-444');

INSERT INTO clients (user_id, business_name, business_type, address, city, contact_person) VALUES
(4, 'Caffe Bar Centar', 'kafic', 'Maršala Tita 15', 'Bosanska Krupa', 'Adnan Kafedžija');

INSERT INTO categories (name, sort_order) VALUES
('Gazirani sokovi', 1), ('Negazirani sokovi', 2), ('Voda', 3),
('Pivo', 4), ('Energetska pića', 5), ('Topli napici', 6);

INSERT INTO products
(category_id, sku, name, volume_label, units_per_crate, price_piece, price_crate, stock_pieces) VALUES
(1, 'CC-025',  'Coca-Cola staklo',        '0.25L', 24, 1.80, 40.80, 2400),
(1, 'FA-025',  'Fanta Orange staklo',     '0.25L', 24, 1.70, 38.40, 1200),
(1, 'SP-025',  'Sprite staklo',           '0.25L', 24, 1.70, 38.40,  960),
(2, 'CV-JAB',  'Cappy Voćni – Jabuka',    '0.20L', 24, 1.90, 43.20, 1440),
(2, 'CV-NAR',  'Cappy Voćni – Narandža',  '0.20L', 24, 1.90, 43.20, 1440),
(3, 'SB-033',  'Sarajevska voda gaz.',    '0.33L', 24, 1.20, 26.40, 4800),
(3, 'OL-05',   'Olimpija negazirana',     '0.50L', 12, 1.40, 15.60, 2400),
(4, 'SP-05',   'Sarajevsko pivo staklo',  '0.33L', 24, 2.20, 50.40, 3600),
(4, 'TU-05',   'Tuzlanski pilsner',       '0.50L', 20, 2.40, 46.00, 1000),
(5, 'RB-025',  'Red Bull limenka',        '0.25L', 24, 3.50, 81.60,  720),
(6, 'KV-1KG',  'Kafa mljevena Zlatna',    '1kg',   12, 12.00, 138.00, 240);