-- db/schema.sql
CREATE DATABASE fila_db;

USE fila_db;

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    priority BOOLEAN NOT NULL DEFAULT 0,
    code VARCHAR(10) NOT NULL,
    shortcut_key VARCHAR(10) NOT NULL,
    counter_name VARCHAR(255) NOT NULL
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT 0
);

CREATE TABLE user_departments (
    user_id INT NOT NULL,
    department_id INT NOT NULL,
    PRIMARY KEY (user_id, department_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE kiosks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    departments TEXT NOT NULL,
    message TEXT
);

CREATE TABLE displays (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    departments TEXT NOT NULL,
    message TEXT
);

CREATE TABLE queues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT NOT NULL,
    number INT NOT NULL,
    called_at TIMESTAMP NULL,
    counter_name VARCHAR(255),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);
