CREATE DATABASE IF NOT EXISTS ichq_mysel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ichq_mysel;

CREATE TABLE departments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  owner_name VARCHAR(150) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_departments_name (name)
);

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  department_id INT UNSIGNED DEFAULT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) DEFAULT NULL,
  microsoft_oid VARCHAR(100) DEFAULT NULL,
  job_title VARCHAR(150) DEFAULT NULL,
  role ENUM('admin','executive','manager','employee') NOT NULL DEFAULT 'employee',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_microsoft_oid (microsoft_oid),
  CONSTRAINT fk_users_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

CREATE TABLE fiscal_years (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(20) NOT NULL,
  fy_year SMALLINT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fy_year (fy_year)
);

CREATE TABLE months (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fiscal_year_id INT UNSIGNED NOT NULL,
  month_number TINYINT UNSIGNED NOT NULL,
  month_name VARCHAR(20) NOT NULL,
  month_start DATE NOT NULL,
  month_end DATE NOT NULL,
  quarter_label VARCHAR(5) NOT NULL,
  UNIQUE KEY uq_months_fy_month (fiscal_year_id, month_number),
  CONSTRAINT fk_months_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE CASCADE
);

CREATE TABLE scorecard_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_scorecard_categories_name (name)
);

CREATE TABLE scorecard_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  objective_title VARCHAR(255) NOT NULL,
  igb_pillar VARCHAR(255) DEFAULT NULL,
  ed_pillar VARCHAR(100) DEFAULT NULL,
  owner_name VARCHAR(150) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_scorecard_items_category FOREIGN KEY (category_id) REFERENCES scorecard_categories(id) ON DELETE CASCADE
);

CREATE TABLE scorecard_item_results (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scorecard_item_id INT UNSIGNED NOT NULL,
  fiscal_year_id INT UNSIGNED NOT NULL,
  result_value TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_scorecard_item_result (scorecard_item_id, fiscal_year_id),
  CONSTRAINT fk_scorecard_item_results_item FOREIGN KEY (scorecard_item_id) REFERENCES scorecard_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_scorecard_item_results_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE CASCADE
);

CREATE TABLE kpis (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(150) NOT NULL,
  metric_name VARCHAR(255) NOT NULL,
  owner_name VARCHAR(150) DEFAULT NULL,
  metric_type ENUM('number','percent','currency','time','text') NOT NULL DEFAULT 'text',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE kpi_values (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kpi_id INT UNSIGNED NOT NULL,
  fiscal_year_id INT UNSIGNED NOT NULL,
  result_value TEXT DEFAULT NULL,
  numeric_value DECIMAL(18,4) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_kpi_value (kpi_id, fiscal_year_id),
  CONSTRAINT fk_kpi_values_kpi FOREIGN KEY (kpi_id) REFERENCES kpis(id) ON DELETE CASCADE,
  CONSTRAINT fk_kpi_values_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE CASCADE
);

CREATE TABLE performance_goals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  department_id INT UNSIGNED DEFAULT NULL,
  owner_user_id INT UNSIGNED DEFAULT NULL,
  fiscal_year_id INT UNSIGNED DEFAULT NULL,
  performance_measure VARCHAR(255) NOT NULL,
  target_value VARCHAR(100) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  status_label VARCHAR(25) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_performance_goals_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
  CONSTRAINT fk_performance_goals_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_performance_goals_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE SET NULL
);

CREATE TABLE performance_goal_quarters (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  performance_goal_id INT UNSIGNED NOT NULL,
  quarter_label VARCHAR(5) NOT NULL,
  actual_value VARCHAR(100) DEFAULT NULL,
  ytd_percent_to_target DECIMAL(10,4) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  UNIQUE KEY uq_goal_quarter (performance_goal_id, quarter_label),
  CONSTRAINT fk_performance_goal_quarters_goal FOREIGN KEY (performance_goal_id) REFERENCES performance_goals(id) ON DELETE CASCADE
);

CREATE TABLE monthly_reports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  department_id INT UNSIGNED DEFAULT NULL,
  owner_user_id INT UNSIGNED DEFAULT NULL,
  fiscal_year_id INT UNSIGNED NOT NULL,
  month_id INT UNSIGNED DEFAULT NULL,
  report_title VARCHAR(255) NOT NULL,
  summary_text LONGTEXT DEFAULT NULL,
  submitted_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_monthly_reports_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
  CONSTRAINT fk_monthly_reports_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_monthly_reports_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id) ON DELETE CASCADE,
  CONSTRAINT fk_monthly_reports_month FOREIGN KEY (month_id) REFERENCES months(id) ON DELETE SET NULL
);

CREATE TABLE monthly_report_metrics (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  monthly_report_id INT UNSIGNED NOT NULL,
  key_result VARCHAR(255) NOT NULL,
  target_value VARCHAR(100) DEFAULT NULL,
  measure_label VARCHAR(255) DEFAULT NULL,
  actual_value VARCHAR(255) DEFAULT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_monthly_report_metrics_report FOREIGN KEY (monthly_report_id) REFERENCES monthly_reports(id) ON DELETE CASCADE
);

CREATE TABLE key_stats (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  objective_name VARCHAR(255) NOT NULL,
  stat_name VARCHAR(255) NOT NULL,
  goal_value VARCHAR(100) DEFAULT NULL,
  measure_label VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE key_stat_values (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_stat_id INT UNSIGNED NOT NULL,
  month_label VARCHAR(20) NOT NULL,
  month_date DATE DEFAULT NULL,
  value_text VARCHAR(255) DEFAULT NULL,
  numeric_value DECIMAL(18,4) DEFAULT NULL,
  CONSTRAINT fk_key_stat_values_stat FOREIGN KEY (key_stat_id) REFERENCES key_stats(id) ON DELETE CASCADE
);

CREATE TABLE strategic_projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  department_id INT UNSIGNED DEFAULT NULL,
  project_title VARCHAR(255) NOT NULL,
  strategic_area VARCHAR(255) DEFAULT NULL,
  ichq_lead VARCHAR(150) DEFAULT NULL,
  executive_sponsor VARCHAR(150) DEFAULT NULL,
  priority_level VARCHAR(25) DEFAULT NULL,
  timeframe_label VARCHAR(50) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  is_complete TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_strategic_projects_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

CREATE TABLE audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED DEFAULT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id BIGINT UNSIGNED DEFAULT NULL,
  action_type VARCHAR(50) NOT NULL,
  action_details JSON DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);