-- Enhanced Connecto Database Schema
CREATE TABLE IF NOT EXISTS `towns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `town_name` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `towns` (`town_name`) VALUES 
('Kroonstad'), ('Bloemfontein'), ('Welkom'), ('Sasolburg'), ('Heilbron'), 
('Parys'), ('All Towns');

CREATE TABLE IF NOT EXISTS `skybre_clients` (
  `client_code` VARCHAR(50) NOT NULL PRIMARY KEY,
  `full_name` VARCHAR(150),
  `town_id` INT NULL,
  FOREIGN KEY (`town_id`) REFERENCES `towns`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `skybre_clients` (`client_code`, `full_name`, `town_id`) VALUES
('SKY100', 'John Smith', 1),
('SKY101', 'Maria Van Wyk', 1),
('SKY102', 'Thabo Nkosi', 2),
('SKY103', 'Anna Botha', 3);

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `role` ENUM('client', 'staff', 'admin') NOT NULL DEFAULT 'client',
  `name` VARCHAR(100) NOT NULL,
  `surname` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(50) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `skybre_client_code` VARCHAR(50) NULL,
  `town_id` INT NULL,
  `fcm_token` TEXT NULL,
  `status` ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`skybre_client_code`) REFERENCES `skybre_clients`(`client_code`),
  FOREIGN KEY (`town_id`) REFERENCES `towns`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tickets
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_number` VARCHAR(20) UNIQUE NOT NULL,
  `user_id` INT NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `category` VARCHAR(100) NULL,
  `status` ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ticket Replies
CREATE TABLE IF NOT EXISTS `ticket_replies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Internal Comments (Staff only)
CREATE TABLE IF NOT EXISTS `client_comments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `client_id` INT NOT NULL,
  `staff_id` INT NOT NULL,
  `comment` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `users`(`id`),
  FOREIGN KEY (`staff_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample Users (password = 'password')
INSERT INTO `users` (`role`, `name`, `surname`, `phone`, `email`, `password`, `town_id`) VALUES
('admin', 'Admin', 'User', '0000000000', 'admin@skybre.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
('staff', 'Support', 'Staff', '1111111111', 'support@skybre.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);