CREATE DATABASE IF NOT EXISTS ai_kuca_sa_stilom
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ai_kuca_sa_stilom;

-- Korisnici

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,

    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,

    phone VARCHAR(50) NULL,

    address VARCHAR(255) NULL,
    city VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,

    role ENUM(
        'super_admin',
        'admin',
        'user'
    ) DEFAULT 'user',

    status ENUM(
        'active',
        'blocked',
        'pending'
    ) DEFAULT 'active',

    can_upload TINYINT(1) DEFAULT 0,

    can_chat_upload TINYINT(1) DEFAULT 0,

    auto_approve_comments TINYINT(1) DEFAULT 0,

    last_login DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

-- Reset lozinke

CREATE TABLE password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    token VARCHAR(255) NOT NULL,

    expires_at DATETIME NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- Logovi aktivnosti

CREATE TABLE activity_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NULL,

    action VARCHAR(255) NOT NULL,

    ip_address VARCHAR(45) NULL,

    user_agent TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- Sesije korisnika

CREATE TABLE user_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    session_id VARCHAR(255) NOT NULL,

    ip_address VARCHAR(45) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    expires_at DATETIME NULL,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- Notifikacije

CREATE TABLE notifications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    title VARCHAR(255) NOT NULL,

    content TEXT NOT NULL,

    is_read TINYINT(1) DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- Podešavanja sistema

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) NOT NULL UNIQUE,

    setting_value LONGTEXT NULL
);

-- Početni super administrator

INSERT INTO users (
    username,
    email,
    password,
    first_name,
    last_name,
    role,
    status
)
VALUES (
    'admin',
    'admin@kucasastilom.rs',

    '$2y$10$abcdefghijklmnopqrstuvabcdefghijklmnopqrstuvabcdefghijkl',

    'Glavni',
    'Administrator',

    'super_admin',
    'active'
);