-- SmartNet Ticket/CRM schema
CREATE DATABASE IF NOT EXISTS smartnet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smartnet;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  phone VARCHAR(30),
  email VARCHAR(100),
  role ENUM('admin','operator','technician') DEFAULT 'operator',
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(150),
  user_phone VARCHAR(40),
  address VARCHAR(255),
  latitude DOUBLE,
  longitude DOUBLE,
  type VARCHAR(50),
  priority ENUM('low','normal','high') DEFAULT 'normal',
  description TEXT,
  status ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
  operator_id INT,
  technician_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE ticket_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  user_id INT,
  message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);

CREATE TABLE uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT,
  filename VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);

-- demo admin user: password 'adminpass' (bcrypt recommended to set manually)
-- You should create users via SQL or admin UI.
