CREATE DATABASE IF NOT EXISTS royal_transport;
USE royal_transport;

CREATE TABLE IF NOT EXISTS komitenti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    naziv VARCHAR(255) NOT NULL,
    grad VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    telefon VARCHAR(50),
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user', 'vozac') NOT NULL DEFAULT 'user',
    komitent_id INT,
    active BOOLEAN DEFAULT TRUE,
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (komitent_id) REFERENCES komitenti(id)
);

CREATE TABLE IF NOT EXISTS voznje (
    id INT AUTO_INCREMENT PRIMARY KEY,
    datum DATE NOT NULL,
    vreme TIME NOT NULL,
    broj_leta VARCHAR(100),
    polazna_adresa VARCHAR(255) NOT NULL,
    dolazna_adresa VARCHAR(255) NOT NULL,
    ime_klijenta VARCHAR(255) NOT NULL,
    vrsta_vozila VARCHAR(100) NOT NULL,
    komitent_id INT,
    dodaci_vozilo VARCHAR(100),
    napomena TEXT,
    placanje VARCHAR(100),
    cena VARCHAR(100),
    vozac_id INT,
    troskovi TEXT,
    komentar TEXT,
    attachment VARCHAR(255),
    status VARCHAR(100) DEFAULT 'Nerasporedjeno',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (komitent_id) REFERENCES komitenti(id),
    FOREIGN KEY (vozac_id) REFERENCES users(id)
);

-- Rename narucilac_firma to komitent_id in voznje table
ALTER TABLE voznje CHANGE COLUMN narucilac_firma komitent_id INT;

-- Insert admin user
INSERT INTO users (email, password, name, role, active) VALUES 
('admin@royaltransport.com', '$2y$10$YourNewHashHere', 'Administrator', 'admin', 1); 