CREATE TABLE IF NOT EXISTS sasp_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  discord_id VARCHAR(32) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL,
  global_name VARCHAR(150) NULL,
  avatar_url VARCHAR(255) NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_ranks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  hierarchy_order INT NOT NULL DEFAULT 999,
  is_admin_rank TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_divisions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  min_rank VARCHAR(120) NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_personnel (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  discord_id VARCHAR(32) NOT NULL UNIQUE,
  badge_no VARCHAR(32) NOT NULL UNIQUE,
  ic_name VARCHAR(160) NOT NULL,
  rank_id INT UNSIGNED NULL,
  division_id INT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'offline',
  discord_activity VARCHAR(120) NULL,
  role_tags JSON NULL,
  permissions JSON NULL,
  panel_access TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_personnel_user FOREIGN KEY (user_id) REFERENCES sasp_users(id) ON DELETE SET NULL,
  CONSTRAINT fk_sasp_personnel_rank FOREIGN KEY (rank_id) REFERENCES sasp_ranks(id) ON DELETE SET NULL,
  CONSTRAINT fk_sasp_personnel_division FOREIGN KEY (division_id) REFERENCES sasp_divisions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_duty_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  started_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  close_reason VARCHAR(255) NULL,
  required_activity VARCHAR(120) NOT NULL DEFAULT 'Social V',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_duty_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_applications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  applicant_name VARCHAR(160) NOT NULL,
  discord_tag VARCHAR(160) NULL,
  type VARCHAR(120) NOT NULL DEFAULT 'SASP Başvurusu',
  status VARCHAR(80) NOT NULL DEFAULT 'Yeni',
  answer_json JSON NULL,
  reviewer_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_app_reviewer FOREIGN KEY (reviewer_id) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_complaints (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  complainant_name VARCHAR(160) NOT NULL,
  target VARCHAR(160) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Yeni',
  summary TEXT NULL,
  evidence_url VARCHAR(255) NULL,
  reviewer_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_complaint_reviewer FOREIGN KEY (reviewer_id) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_excuses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  reason TEXT NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Bekliyor',
  until_text VARCHAR(160) NULL,
  reviewed_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_excuse_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_sasp_excuse_reviewer FOREIGN KEY (reviewed_by) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id INT UNSIGNED NOT NULL,
  type VARCHAR(140) NOT NULL,
  classification VARCHAR(80) NOT NULL DEFAULT 'UNCLASSIFIED',
  subject VARCHAR(255) NULL,
  body LONGTEXT NULL,
  saved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_doc_author FOREIGN KEY (author_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_training_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  trainer_id INT UNSIGNED NULL,
  type VARCHAR(120) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Bekliyor',
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_training_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_sasp_training_trainer FOREIGN KEY (trainer_id) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_discord_channels (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel_key VARCHAR(80) NOT NULL UNIQUE,
  channel_name VARCHAR(120) NOT NULL,
  discord_channel_id VARCHAR(32) NULL,
  topic VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_id INT UNSIGNED NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  action VARCHAR(180) NOT NULL,
  payload_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_audit_actor FOREIGN KEY (actor_id) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_homepage_settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value LONGTEXT NULL,
  updated_by INT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_homepage_actor FOREIGN KEY (updated_by) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_gallery_assets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  description TEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_panel_permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  module_key VARCHAR(80) NOT NULL,
  can_view TINYINT(1) NOT NULL DEFAULT 1,
  can_manage TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_sasp_panel_permission (personnel_id, module_key),
  CONSTRAINT fk_sasp_panel_permission_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_ribbons (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ribbon_key VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_personnel_ribbons (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  ribbon_id INT UNSIGNED NOT NULL,
  awarded_by INT UNSIGNED NULL,
  note TEXT NULL,
  awarded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_sasp_personnel_ribbon (personnel_id, ribbon_id),
  CONSTRAINT fk_sasp_personnel_ribbon_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_sasp_personnel_ribbon_ribbon FOREIGN KEY (ribbon_id) REFERENCES sasp_ribbons(id) ON DELETE CASCADE,
  CONSTRAINT fk_sasp_personnel_ribbon_actor FOREIGN KEY (awarded_by) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_payroll_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  period_label VARCHAR(120) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'paid',
  paid_by INT UNSIGNED NULL,
  paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  discord_log_channel VARCHAR(80) NOT NULL DEFAULT 'maas-log',
  CONSTRAINT fk_sasp_payroll_personnel FOREIGN KEY (personnel_id) REFERENCES sasp_personnel(id) ON DELETE CASCADE,
  CONSTRAINT fk_sasp_payroll_actor FOREIGN KEY (paid_by) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sasp_report_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  period_type ENUM('weekly','monthly') NOT NULL,
  period_label VARCHAR(120) NOT NULL,
  payload_json JSON NOT NULL,
  generated_by INT UNSIGNED NULL,
  generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sasp_report_actor FOREIGN KEY (generated_by) REFERENCES sasp_personnel(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO sasp_ranks (id, name, hierarchy_order, is_admin_rank) VALUES
  (1, 'Cadet', 10, 1),
  (2, 'Trooper 1', 20, 1),
  (3, 'Trooper 2', 30, 1),
  (4, 'Trooper 3', 40, 1),
  (5, 'Corporal', 50, 1),
  (6, 'Sergeant 1', 60, 0),
  (7, 'Sergeant 2', 70, 0),
  (8, 'Lieutenant 1', 80, 0),
  (9, 'Lieutenant 2', 90, 0),
  (10, 'Captain', 100, 0),
  (11, 'Commander', 110, 0),
  (12, 'Deputy Cheif', 120, 0),
  (13, 'Assistant Cheif', 130, 0),
  (14, 'Cheif Of State', 140, 0);

INSERT IGNORE INTO sasp_homepage_settings (setting_key, setting_value) VALUES
  ('staffVisible', 'true'),
  ('divisionsVisible', 'true'),
  ('title', 'San Andreas State Police'),
  ('eyebrow', 'State of San Andreas • SASP Headquarters'),
  ('heroLead', 'SASP bünyesindeki eyalet trooperları, asayiş, devriye, özel operasyon birimleri ve resmi evrak yönetim portalı.');

INSERT IGNORE INTO sasp_ribbons (id, ribbon_key, name, image_path, description) VALUES
  (1, 'top-duty', 'Top Duty Ribbon', 'assets/img/sasp-seal.png', 'Haftalık en yüksek mesai performansı.'),
  (2, 'patrol-excellence', 'Patrol Excellence Ribbon', 'assets/img/sasp-logo-transparent.png', 'Olay yeri, devriye ve belge disiplini başarısı.');

INSERT IGNORE INTO sasp_divisions (id, code, name, status, min_rank, description) VALUES
  (1, 'hwp', 'Highway Patrol Division', 'open', 'Trooper', 'Eyalet geneli otoban devriyesi ve hız ihlalleri.'),
  (2, 'sru', 'Special Response Unit (SRU)', 'open', 'Trooper', 'Yüksek riskli operasyonlar ve taktiksel müdahaleler.'),
  (3, 'k9', 'K9 Operations Unit', 'open', 'Trooper', 'Köpekli devriye, uyuşturucu ve patlayıcı aramaları.'),
  (4, 'iad', 'Internal Affairs Division (IAD)', 'open', 'Senior Trooper', 'Şikayet, disiplin, soruşturma ve denetim incelemeleri.'),
  (5, 'fto', 'Training & FTO Division', 'open', 'FTO / Training Officer', 'Eğitim, sınav, akademi ve değerlendirme kayıtları.');

INSERT IGNORE INTO sasp_discord_channels (channel_key, channel_name, topic) VALUES
  ('panel', 'sasp-panel', 'SASP ana bot paneli'),
  ('duty', 'mesai-giris-cikis', 'Mesai giriş/çıkış logları'),
  ('excuse', 'mazeret-bildirimleri', 'Personel mazeret bildirimleri'),
  ('complaint', 'sikayet-loglari', 'Internal Affairs şikayet logları'),
  ('document', 'belge-loglari', 'Resmi belge kayıt logları'),
  ('application', 'basvuru-loglari', 'Başvuru form logları'),
  ('payroll', 'maas-log', 'Maaş ödeme logları'),
  ('admin', 'discord-yonetim-log', 'Kanal/rol yönetim logları');

INSERT IGNORE INTO sasp_personnel
  (id, discord_id, badge_no, ic_name, rank_id, division_id, status, discord_activity, role_tags, permissions, panel_access)
VALUES
  (1, '269158973645651968', '101', 'Dean Teller', 9, 1, 'offline', 'Offline', JSON_ARRAY('San Andreas State Police', 'Chief Of State'), JSON_ARRAY('admin','admin.access'), 1),
  (2, '323931260231876620', '732', 'Lucas Teller', 3, 1, 'offline', 'Offline', JSON_ARRAY('San Andreas State Police', 'Sergeant 1'), JSON_ARRAY('admin', 'admin.access'), 1),
  (3, '804846177962426378', '103', 'Robin Crowe', 13, 5, 'offline', 'Offline', JSON_ARRAY('San Andreas State Police', 'Assistant Cheif'), JSON_ARRAY('admin', 'admin.access'), 1);