-- database.sql

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

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','user') NOT NULL DEFAULT 'user',
    can_projects  TINYINT(1) NOT NULL DEFAULT 1,
    can_items     TINYINT(1) NOT NULL DEFAULT 1,
    can_orders    TINYINT(1) NOT NULL DEFAULT 1,
    can_suppliers TINYINT(1) NOT NULL DEFAULT 1,
    can_receipts  TINYINT(1) NOT NULL DEFAULT 1,
    can_stock     TINYINT(1) NOT NULL DEFAULT 1
);

-- Demo korisnik (ZAMIJENI HASH!)
INSERT INTO users (username, password_hash, role,
                   can_projects, can_items, can_orders,
                   can_suppliers, can_receipts, can_stock)
VALUES ('admin', '$2y$10$examplehashOVDJepromijeniRucnoXXXXXX', 'admin',
        1,1,1,1,1,1);

CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    location VARCHAR(150),
    description TEXT,
    project_status ENUM('open','closed') NOT NULL DEFAULT 'open'
);

CREATE TABLE IF NOT EXISTS item_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    item_group_id INT NULL,
    unit VARCHAR(20) NOT NULL,
    FOREIGN KEY (item_group_id) REFERENCES item_groups(id)
);

CREATE TABLE IF NOT EXISTS consumption_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    usage_date DATE NOT NULL,
    note VARCHAR(255),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS consumption_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES consumption_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);

CREATE TABLE IF NOT EXISTS suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    oib VARCHAR(20),
    address VARCHAR(200),
    city VARCHAR(100),
    phone VARCHAR(50),
    email VARCHAR(100),
    note VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_id INT NOT NULL,
    receipt_date DATE NOT NULL,
    doc_no VARCHAR(50),
    note VARCHAR(255),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE IF NOT EXISTS receipt_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id)
);
