ALTER TABLE sasp_personnel
  ADD COLUMN IF NOT EXISTS photo_url VARCHAR(255) NULL AFTER panel_access,
  ADD COLUMN IF NOT EXISTS allowed_panels JSON NULL AFTER permissions,
  ADD COLUMN IF NOT EXISTS denied_panels JSON NULL AFTER allowed_panels;

ALTER TABLE sasp_applications
  ADD COLUMN IF NOT EXISTS division_id INT UNSIGNED NULL AFTER type,
  ADD COLUMN IF NOT EXISTS source VARCHAR(80) NOT NULL DEFAULT 'web_panel' AFTER division_id;

ALTER TABLE sasp_divisions
  ADD COLUMN IF NOT EXISTS is_public TINYINT(1) NOT NULL DEFAULT 1 AFTER status;

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 INTO sasp_homepage_settings (setting_key, setting_value) VALUES
('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ı.'),
('missionText', 'San Andreas State Police çatısı altında SASP operasyonları, birim koordinasyonları, Internal Affairs dosyaları ve personel yetki akışları tek sistemden yönetilir.'),
('staffVisible', 'true'),
('divisionsVisible', 'true')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO sasp_discord_channels (channel_key, channel_name, topic) VALUES
('payroll', 'maas-log', 'San Andreas State Police maas odeme loglari')
ON DUPLICATE KEY UPDATE channel_name = VALUES(channel_name), topic = VALUES(topic);

UPDATE sasp_personnel
SET role_tags = JSON_REPLACE(role_tags, '$[0]', 'San Andreas State Police')
WHERE JSON_VALID(role_tags) AND (
  JSON_SEARCH(role_tags, 'one', 'Department Of Justice') IS NOT NULL OR 
  JSON_SEARCH(role_tags, 'one', 'Empire Of Justice') IS NOT NULL
);

UPDATE sasp_personnel p
LEFT JOIN sasp_ranks r ON r.name = 'Major'
SET p.permissions = JSON_ARRAY('admin', 'admin.access'),
    p.role_tags = JSON_ARRAY('San Andreas State Police', 'High Command', 'SASP Patrol'),
    p.rank_id = COALESCE(r.id, p.rank_id),
    p.discord_id = '825447577750470696',
    p.panel_access = 1
WHERE p.ic_name = 'Lucas Teller' OR p.badge_no = '732';

UPDATE sasp_personnel
SET permissions = JSON_ARRAY('personnel'),
    role_tags = JSON_ARRAY('San Andreas State Police', 'SASP Patrol')
WHERE ic_name = 'Dean Teller' OR badge_no = '101';