-- 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);

-- 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;
