-- SKG Job Panel Database Schema
-- This creates the skg_job_panel database tables for faction management

-- Create database if not exists (run this manually if needed)
-- CREATE DATABASE IF NOT EXISTS skg_job_panel CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Job Panels (Factions)
CREATE TABLE IF NOT EXISTS job_panels (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  job_name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT,
  logo_url VARCHAR(500),
  color VARCHAR(7) DEFAULT '#3b82f6',
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_by VARCHAR(36),
  
  INDEX idx_job_name (job_name),
  INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Panel Features (enabled features per panel)
CREATE TABLE IF NOT EXISTS panel_features (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  feature_type VARCHAR(50) NOT NULL,
  is_enabled BOOLEAN DEFAULT TRUE,
  config JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  UNIQUE KEY unique_panel_feature (panel_id, feature_type),
  INDEX idx_panel_id (panel_id),
  INDEX idx_feature_type (feature_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feature Permissions (which ranks can access which features)
CREATE TABLE IF NOT EXISTS feature_permissions (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  feature_type VARCHAR(50) NOT NULL,
  min_rank INT DEFAULT 0,
  allowed_ranks JSON,
  permissions JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  UNIQUE KEY unique_panel_feature_perm (panel_id, feature_type),
  INDEX idx_panel_id (panel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Discord Webhooks
CREATE TABLE IF NOT EXISTS panel_webhooks (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  name VARCHAR(100) NOT NULL,
  webhook_url VARCHAR(500) NOT NULL,
  events JSON NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  last_used_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Criminal Records (for police/justice factions)
CREATE TABLE IF NOT EXISTS criminal_records (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  citizen_id VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  offenses JSON,
  total_fines INT DEFAULT 0,
  total_jail_time INT DEFAULT 0,
  wanted_level INT DEFAULT 0,
  notes TEXT,
  created_by VARCHAR(50),
  created_by_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_citizen_id (citizen_id),
  INDEX idx_wanted_level (wanted_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Panel Vehicles (faction vehicles)
CREATE TABLE IF NOT EXISTS panel_vehicles (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  plate VARCHAR(20) NOT NULL,
  model VARCHAR(50) NOT NULL,
  label VARCHAR(100),
  status ENUM('available', 'in_use', 'damaged', 'impounded') DEFAULT 'available',
  assigned_to VARCHAR(50),
  assigned_to_name VARCHAR(100),
  last_used_at TIMESTAMP NULL,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_plate (plate),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Duty Roster / Shifts
CREATE TABLE IF NOT EXISTS duty_roster (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  citizen_id VARCHAR(50) NOT NULL,
  employee_name VARCHAR(100),
  clock_in TIMESTAMP NOT NULL,
  clock_out TIMESTAMP NULL,
  total_minutes INT DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_citizen_id (citizen_id),
  INDEX idx_clock_in (clock_in)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Announcements
CREATE TABLE IF NOT EXISTS panel_announcements (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
  is_pinned BOOLEAN DEFAULT FALSE,
  created_by VARCHAR(50),
  created_by_name VARCHAR(100),
  expires_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_priority (priority),
  INDEX idx_is_pinned (is_pinned)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Warehouse / Storage Items
CREATE TABLE IF NOT EXISTS panel_warehouse (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  item_name VARCHAR(100) NOT NULL,
  item_label VARCHAR(100),
  quantity INT DEFAULT 0,
  min_quantity INT DEFAULT 0,
  last_restocked_at TIMESTAMP NULL,
  last_restocked_by VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  UNIQUE KEY unique_panel_item (panel_id, item_name),
  INDEX idx_panel_id (panel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Finance Transactions
CREATE TABLE IF NOT EXISTS panel_finances (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  type ENUM('deposit', 'withdrawal', 'salary', 'purchase', 'other') NOT NULL,
  amount INT NOT NULL,
  balance_after INT NOT NULL,
  description VARCHAR(255),
  created_by VARCHAR(50),
  created_by_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_type (type),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Panel Documents
CREATE TABLE IF NOT EXISTS panel_documents (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  category VARCHAR(50),
  is_template BOOLEAN DEFAULT FALSE,
  created_by VARCHAR(50),
  created_by_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_category (category),
  INDEX idx_is_template (is_template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Log for Job Panels
CREATE TABLE IF NOT EXISTS panel_audit_log (
  id VARCHAR(36) PRIMARY KEY,
  panel_id VARCHAR(36) NOT NULL,
  action VARCHAR(100) NOT NULL,
  target_type VARCHAR(50),
  target_id VARCHAR(50),
  actor_citizen_id VARCHAR(50),
  actor_name VARCHAR(100),
  details JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (panel_id) REFERENCES job_panels(id) ON DELETE CASCADE,
  INDEX idx_panel_id (panel_id),
  INDEX idx_action (action),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
