-- FIAC Database Schema for XAMPP MySQL
-- Run this in phpMyAdmin or MySQL CLI after starting XAMPP

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

USE fiac_db;

CREATE TABLE IF NOT EXISTS roles (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  slug VARCHAR(50) NOT NULL,
  level TINYINT UNSIGNED NOT NULL COMMENT 'Lower number = higher authority',
  description VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_slug (slug),
  UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id TINYINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  phone VARCHAR(20) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_role_id (role_id),
  KEY idx_users_created_by (created_by),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles (id),
  CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (name, slug, level, description) VALUES
  ('Master Admin', 'master_admin', 1, 'Full system control, can create/assign all internal roles'),
  ('Admin',        'admin',        2, 'Can create/assign advisor, driver, staff, parts, accountant'),
  ('Advisor',      'advisor',      3, 'Service advisor. Accepts bookings, inspects cars, and assigns drivers and staff.'),
  ('Staff',        'staff',        4, 'Workshop technician. Repair work only.'),
  ('Driver',       'driver',       4, 'Picks up the car from the customer and delivers it back.'),
  ('Parts',        'parts',        4, 'Parts department user'),
  ('Accountant',   'accountant',   4, 'Accounts department user'),
  ('Customer',     'customer',     5, 'Self-registration only')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  level = VALUES(level),
  description = VALUES(description);