DROP DATABASE IF EXISTS fcst_shortcut_calendar;
CREATE DATABASE fcst_shortcut_calendar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fcst_shortcut_calendar;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('superadmin','admin') NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE system_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_bot_token TEXT NULL,
  telegram_chat_id VARCHAR(100) NULL,
  mail_host VARCHAR(120) DEFAULT 'smtp.gmail.com',
  mail_port INT DEFAULT 587,
  mail_username VARCHAR(190) NULL,
  mail_app_password TEXT NULL,
  mail_from_address VARCHAR(190) NULL,
  mail_from_name VARCHAR(190) DEFAULT 'FCST Shortcut Calendar',
  ai_enabled TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE shortcut_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  url TEXT NOT NULL,
  category VARCHAR(100) NULL,
  ai_summary TEXT NULL,
  click_count INT UNSIGNED DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_shortcut_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE calendar_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  event_date DATE NOT NULL,
  start_time TIME NOT NULL,
  duration_minutes INT NOT NULL DEFAULT 60,
  venue VARCHAR(200) NULL,
  remarks TEXT NULL,
  ai_priority ENUM('Low','Normal','High','Urgent') DEFAULT 'Normal',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_event_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_event_date (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (name,email,password,role,is_active) VALUES
('Superadmin','superadmin@fcuc.edu.my','$2y$12$9n7pJD.UBg1e.oJbYYBMjOxQkk9wIv6V8lLE4xY8nMSEdcY6z.C8e','superadmin',1),
('Admin User','admin@fcuc.edu.my','$2y$12$9n7pJD.UBg1e.oJbYYBMjOxQkk9wIv6V8lLE4xY8nMSEdcY6z.C8e','admin',1);

INSERT INTO system_settings (telegram_chat_id, mail_from_address) VALUES ('','fcst@example.com');

INSERT INTO shortcut_links (user_id,title,url,category,ai_summary) VALUES
(1,'First City University College','https://firstcity.edu.my','University','Official FCUC website'),
(1,'FCST Key Movement Log','https://ai365.com.my/fcstkey','Faculty System','Quick access to key movement log');

INSERT INTO calendar_events (user_id,title,event_date,start_time,duration_minutes,venue,remarks,ai_priority) VALUES
(1,'Faculty Meeting',CURDATE(),'10:00:00',60,'FCST Meeting Room','Monthly faculty coordination meeting','High');