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