-- ============================================
-- Player Reports Database Tables
-- ============================================

-- add it to your fivem sql

-- Rule violations configuration table
CREATE TABLE IF NOT EXISTS `rule_violations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `severity` ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Player reports table
CREATE TABLE IF NOT EXISTS `player_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reported_license` varchar(255) NOT NULL,
  `reported_name` varchar(255) NOT NULL,
  `reported_discord` varchar(255) DEFAULT NULL,
  `reporter_username` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` ENUM('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED') DEFAULT 'OPEN',
  `priority` ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
  `images_json` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolved_by` varchar(255) DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reported_license` (`reported_license`),
  KEY `reporter_username` (`reporter_username`),
  KEY `status` (`status`),
  KEY `priority` (`priority`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional normalized table to store images per report
CREATE TABLE IF NOT EXISTS `player_report_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `path` varchar(512) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`),
  CONSTRAINT `fk_report_images_report` FOREIGN KEY (`report_id`) REFERENCES `player_reports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Player report rule violations (many-to-many relationship)
CREATE TABLE IF NOT EXISTS `player_report_violations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `violation_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `report_id` (`report_id`),
  KEY `violation_id` (`violation_id`),
  CONSTRAINT `fk_report_violations_report` FOREIGN KEY (`report_id`) REFERENCES `player_reports` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_report_violations_violation` FOREIGN KEY (`violation_id`) REFERENCES `rule_violations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert some default rule violations
INSERT IGNORE INTO `rule_violations` (`name`, `description`, `severity`) VALUES
('RDM (Random Death Match)', 'Killing another player without proper roleplay reason', 'HIGH'),
('VDM (Vehicle Death Match)', 'Using a vehicle to kill or harm another player without roleplay reason', 'HIGH'),
('Metagaming', 'Using out-of-character information in-character', 'MEDIUM'),
('Power Gaming', 'Forcing roleplay on another player or performing unrealistic actions', 'MEDIUM'),
('Fail RP', 'Breaking character or not following roleplay standards', 'MEDIUM'),
('Combat Logging', 'Disconnecting during active roleplay or combat', 'HIGH'),
('Exploiting', 'Using game exploits or bugs for personal advantage', 'CRITICAL'),
('Griefing', 'Intentionally ruining the experience of other players', 'MEDIUM'),
('Trolling', 'Non-serious roleplay or disruptive behavior', 'MEDIUM'),
('NITRP (No Intent to Roleplay)', 'Showing no effort to engage in proper roleplay', 'HIGH'),
('Breaking Character', 'Going out of character in roleplay situations', 'LOW'),
('Inappropriate Behavior', 'Offensive, toxic, or inappropriate conduct', 'HIGH'),
('Stream Sniping', 'Using streamer information to gain unfair advantage', 'MEDIUM'),
('Economy Exploit', 'Exploiting economy systems for unfair gain', 'CRITICAL'),
('Cheating/Hacking', 'Using external tools to gain unfair advantage', 'CRITICAL');

-- ============================================
-- Migration: Update image paths from API to public path
-- ============================================
-- Run these commands to update existing data if you already have reports with images

-- Update paths in player_report_images table
UPDATE `player_report_images` 
SET `path` = REPLACE(`path`, '/api/player-reports/images/', '/public/player-reports/images/') 
WHERE `path` LIKE '/api/player-reports/images/%';

-- Update images_json in player_reports table (JSON format)
UPDATE `player_reports` 
SET `images_json` = JSON_SET(`images_json`, '$[*]', 
    JSON_UNQUOTE(JSON_EXTRACT(
        JSON_ARRAY_REPLACE(`images_json`, '$[*]', 
            REPLACE(JSON_UNQUOTE(JSON_EXTRACT(`images_json`, '$[*]')), 
                '/api/player-reports/images/', '/public/player-reports/images/')
        ), '$[*]'
    ))
)
WHERE `images_json` IS NOT NULL 
AND JSON_EXTRACT(`images_json`, '$[0]') LIKE '%/api/player-reports/images/%';

-- Alternative update for TEXT format images_json (for older MySQL/MariaDB without JSON functions)
UPDATE `player_reports` 
SET `images_json` = REPLACE(`images_json`, '/api/player-reports/images/', '/public/player-reports/images/')
WHERE `images_json` IS NOT NULL 
AND `images_json` LIKE '%/api/player-reports/images/%';
