-- Create database and user (adjust host as needed)
CREATE DATABASE IF NOT EXISTS `securitisys_greg` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user if it doesn't exist and grant privileges
CREATE USER IF NOT EXISTS 'securitisys_greg'@'localhost' IDENTIFIED BY 'securitisys_greg';
CREATE USER IF NOT EXISTS 'securitisys_greg'@'%' IDENTIFIED BY 'securitisys_greg';
GRANT ALL PRIVILEGES ON `securitisys_greg`.* TO 'securitisys_greg'@'localhost';
GRANT ALL PRIVILEGES ON `securitisys_greg`.* TO 'securitisys_greg'@'%';
FLUSH PRIVILEGES;

USE `securitisys_greg`;

-- Users table: supports local credentials and Google OAuth
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NULL UNIQUE,
  `email` VARCHAR(255) NULL UNIQUE,
  `password_hash` VARCHAR(255) NULL,
  `google_id` VARCHAR(255) NULL,
  `name` VARCHAR(255) NULL,
  `image` VARCHAR(1024) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_users_email` (`email`),
  INDEX `idx_users_username` (`username`),
  INDEX `idx_users_google_id` (`google_id`)
) ENGINE=InnoDB;

-- Conversations table
CREATE TABLE IF NOT EXISTS `conversations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `public_id` VARCHAR(64) NOT NULL UNIQUE,
  `title` VARCHAR(255) NOT NULL DEFAULT 'New Conversation',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_conversations_user_id` (`user_id`),
  INDEX `idx_conversations_public_id` (`public_id`),
  INDEX `idx_conversations_updated_at` (`updated_at`),
  CONSTRAINT `fk_conversations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Messages table
CREATE TABLE IF NOT EXISTS `messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` BIGINT UNSIGNED NOT NULL,
  `role` ENUM('user','assistant') NOT NULL,
  `content` MEDIUMTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_messages_conversation_id` (`conversation_id`),
  INDEX `idx_messages_created_at` (`created_at`),
  CONSTRAINT `fk_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Optional: simple view for conversation summaries
DROP VIEW IF EXISTS `v_conversation_summaries`;
CREATE VIEW `v_conversation_summaries` AS
SELECT c.id, c.user_id, c.title, c.updated_at
FROM conversations c;