-- init_db.sql
CREATE DATABASE IF NOT EXISTS report_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE report_system;

-- users
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  fullname VARCHAR(150),
  role ENUM('viewer','editor','admin') DEFAULT 'editor',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- raw reports
CREATE TABLE IF NOT EXISTS raw_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uploaded_by INT,
  filename VARCHAR(255),
  content LONGTEXT,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- pms_backlog
CREATE TABLE IF NOT EXISTS pms_backlog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  property_id INT,
  prop_name VARCHAR(255),
  count_value INT,
  region VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);

-- offline_rt1000
CREATE TABLE IF NOT EXISTS offline_rt1000 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  property_id INT,
  prop_name VARCHAR(255),
  rt_name VARCHAR(100),
  duration_minutes INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);

-- cdr_status
CREATE TABLE IF NOT EXISTS cdr_status (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  property_id INT,
  prop_name VARCHAR(255),
  pbx_type VARCHAR(50),
  pbx_number VARCHAR(50),
  pbx_raw_seq VARCHAR(50),
  enterprise_code VARCHAR(50),
  group_code VARCHAR(50),
  last_posted_on DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);

-- wakeup_errors
CREATE TABLE IF NOT EXISTS wakeup_errors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  property_id INT,
  prop_name VARCHAR(255),
  room_number VARCHAR(50),
  attempts INT,
  schedule_time DATETIME NULL,
  wakeup_time DATETIME NULL,
  status_text VARCHAR(255),
  room_status VARCHAR(50),
  pbx_type VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);

-- esb_errors
CREATE TABLE IF NOT EXISTS esb_errors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  service_path VARCHAR(255),
  method VARCHAR(10),
  count INT,
  time_occurred DATETIME,
  status_code INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);

-- system_stats
CREATE TABLE IF NOT EXISTS system_stats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  report_id INT,
  name_display_queue INT NULL,
  message_queue_backlog INT NULL,
  ps01_memory FLOAT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES raw_reports(id) ON DELETE CASCADE
);
