/* State of Crime (Dashboard MVP) schema */
/* Run this in your database before using the site. */
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(32) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  user_level TINYINT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_username (username),
  UNIQUE KEY uniq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ranks (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_stats (
  user_id BIGINT UNSIGNED NOT NULL,
  money BIGINT NOT NULL DEFAULT 0,
  bullets INT NOT NULL DEFAULT 0,
  points INT NOT NULL DEFAULT 0,
  health INT NOT NULL DEFAULT 100,
  max_health INT NOT NULL DEFAULT 100,
  rank_id SMALLINT UNSIGNED NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_stats_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_stats_rank FOREIGN KEY (rank_id) REFERENCES ranks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sender_id BIGINT UNSIGNED NOT NULL,
  recipient_id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(120) NOT NULL DEFAULT '',
  body TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_recipient (recipient_id, is_read, created_at),
  CONSTRAINT fk_msg_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_msg_recipient FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  message VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user (user_id, created_at),
  CONSTRAINT fk_act_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS announcements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(120) NOT NULL,
  body VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Seed ranks (edit whenever) */
INSERT INTO ranks (name, sort_order)
SELECT * FROM (
  SELECT 'Nobody', 1 UNION ALL
  SELECT 'Associate', 2 UNION ALL
  SELECT 'Soldier', 3 UNION ALL
  SELECT 'Capo', 4 UNION ALL
  SELECT 'Underboss', 5 UNION ALL
  SELECT 'Boss', 6
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM ranks LIMIT 1);

/* Seed announcements (optional) */
INSERT INTO announcements (title, body)
SELECT * FROM (
  SELECT 'Welcome', 'State of Crime is under construction. More features landing soon.' UNION ALL
  SELECT 'Tip', 'Crimes, car theft, and organised crimes will sit in the left menu.'
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM announcements LIMIT 1);

/* After you register a user, create their stats row like this: */
/* INSERT INTO user_stats (user_id, money, bullets, points, health, max_health, rank_id) */
/* VALUES (NEW_USER_ID, 250, 0, 0, 100, 100, (SELECT id FROM ranks WHERE name='Nobody' LIMIT 1)); */


/* Crimes */
CREATE TABLE IF NOT EXISTS crimes (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  min_reward INT NOT NULL DEFAULT 10,
  max_reward INT NOT NULL DEFAULT 50,
  success_rate TINYINT UNSIGNED NOT NULL DEFAULT 60, -- 0-100
  cooldown_seconds INT NOT NULL DEFAULT 20,
  min_points INT NOT NULL DEFAULT 1,
  max_points INT NOT NULL DEFAULT 3,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Tracks last attempt per user per crime (for cooldowns) */
CREATE TABLE IF NOT EXISTS user_crime_cooldowns (
  user_id BIGINT UNSIGNED NOT NULL,
  crime_id SMALLINT UNSIGNED NOT NULL,
  last_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, crime_id),
  CONSTRAINT fk_ucc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ucc_crime FOREIGN KEY (crime_id) REFERENCES crimes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Seed starter crimes (only if empty) */
INSERT INTO crimes (name, description, min_reward, max_reward, success_rate, cooldown_seconds, min_points, max_points, sort_order)
SELECT * FROM (
  SELECT 'Pickpocket', 'Light work in a crowded street.', 20, 80, 75, 25, 1, 2, 1 UNION ALL
  SELECT 'Mugging', 'Quick intimidation for a fast payout.', 60, 160, 65, 45, 1, 3, 2 UNION ALL
  SELECT 'Shoplift', 'Small goods, low heat, steady money.', 40, 120, 70, 35, 1, 2, 3 UNION ALL
  SELECT 'Burgle House', 'Bigger risk, bigger reward.', 120, 320, 55, 70, 2, 4, 4 UNION ALL
  SELECT 'Armed Robbery', 'High stakes. Expect trouble.', 220, 520, 45, 110, 3, 6, 5
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM crimes LIMIT 1);


/* Rank bonuses (affect crime success chance) */
ALTER TABLE ranks
  ADD COLUMN rank_bonus TINYINT NOT NULL DEFAULT 0;

/* Set default bonuses (safe if re-run: only updates existing rows) */
UPDATE ranks SET rank_bonus = CASE name
  WHEN 'Nobody' THEN 0
  WHEN 'Associate' THEN 2
  WHEN 'Soldier' THEN 4
  WHEN 'Capo' THEN 6
  WHEN 'Underboss' THEN 8
  WHEN 'Boss' THEN 10
  ELSE 0
END;


/* Jail timer (blocks crimes while active) */
ALTER TABLE user_stats
  ADD COLUMN jail_until TIMESTAMP NULL DEFAULT NULL,
  ADD KEY idx_jail_until (jail_until);


/* Crime heat / jail settings */
ALTER TABLE crimes
  ADD COLUMN jail_chance TINYINT UNSIGNED NOT NULL DEFAULT 20, -- chance on failure (0-100)
  ADD COLUMN jail_seconds INT NOT NULL DEFAULT 60;

/* Set starter crime jail settings (if crimes exist) */
UPDATE crimes SET jail_chance = CASE name
  WHEN 'Pickpocket' THEN 10
  WHEN 'Shoplift' THEN 12
  WHEN 'Mugging' THEN 18
  WHEN 'Burgle House' THEN 28
  WHEN 'Armed Robbery' THEN 40
  ELSE jail_chance
END,
jail_seconds = CASE name
  WHEN 'Pickpocket' THEN 45
  WHEN 'Shoplift' THEN 55
  WHEN 'Mugging' THEN 75
  WHEN 'Burgle House' THEN 120
  WHEN 'Armed Robbery' THEN 180
  ELSE jail_seconds
END;


/* Hospital timer (used for injuries; blocks some actions) */
ALTER TABLE user_stats
  ADD COLUMN hospital_until TIMESTAMP NULL DEFAULT NULL,
  ADD KEY idx_hospital_until (hospital_until);


/* Upgrades */
CREATE TABLE IF NOT EXISTS upgrades (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  max_level TINYINT UNSIGNED NOT NULL DEFAULT 10,
  base_cost INT NOT NULL DEFAULT 100,
  cost_multiplier DECIMAL(6,2) NOT NULL DEFAULT 1.35,
  effect_per_level TINYINT UNSIGNED NOT NULL DEFAULT 1, -- percentage points
  applies_to ENUM('crime','car_theft') NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_upgrades (
  user_id BIGINT UNSIGNED NOT NULL,
  upgrade_id SMALLINT UNSIGNED NOT NULL,
  level TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, upgrade_id),
  CONSTRAINT fk_uup_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uup_upgrade FOREIGN KEY (upgrade_id) REFERENCES upgrades(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Seed upgrades (only if empty) */
INSERT INTO upgrades (code, name, description, max_level, base_cost, cost_multiplier, effect_per_level, applies_to, sort_order)
SELECT * FROM (
  SELECT 'street_smarts', 'Street Smarts', 'Improves success chance for crimes.', 10, 200, 1.45, 1, 'crime', 1 UNION ALL
  SELECT 'driver_skill', 'Driver Skill', 'Improves success chance for car theft.', 10, 250, 1.45, 1, 'car_theft', 2
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM upgrades LIMIT 1);


/* Car Theft */
CREATE TABLE IF NOT EXISTS car_thefts (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  min_reward INT NOT NULL DEFAULT 50,
  max_reward INT NOT NULL DEFAULT 150,
  success_rate TINYINT UNSIGNED NOT NULL DEFAULT 55, -- base 0-100
  cooldown_seconds INT NOT NULL DEFAULT 60,
  min_points INT NOT NULL DEFAULT 2,
  max_points INT NOT NULL DEFAULT 5,
  jail_chance TINYINT UNSIGNED NOT NULL DEFAULT 25,      -- on failure
  jail_seconds INT NOT NULL DEFAULT 120,
  hospital_chance TINYINT UNSIGNED NOT NULL DEFAULT 20,  -- on failure
  hospital_seconds INT NOT NULL DEFAULT 90,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_car_theft_cooldowns (
  user_id BIGINT UNSIGNED NOT NULL,
  car_theft_id SMALLINT UNSIGNED NOT NULL,
  last_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, car_theft_id),
  CONSTRAINT fk_uct_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uct_ct FOREIGN KEY (car_theft_id) REFERENCES car_thefts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Seed starter car theft options (only if empty) */
INSERT INTO car_thefts (name, description, min_reward, max_reward, success_rate, cooldown_seconds, min_points, max_points, jail_chance, jail_seconds, hospital_chance, hospital_seconds, sort_order)
SELECT * FROM (
  SELECT 'Joyride', 'Low-end car. Quick in and out.', 120, 260, 70, 70, 2, 4, 15, 80, 10, 60, 1 UNION ALL
  SELECT 'Saloon Grab', 'Mid-range car with moderate heat.', 220, 420, 58, 110, 3, 6, 25, 140, 18, 90, 2 UNION ALL
  SELECT 'SUV Lift', 'Higher value, tougher security.', 320, 620, 50, 140, 4, 7, 30, 180, 25, 120, 3 UNION ALL
  SELECT 'Luxury Heist', 'Top tier vehicle. Expect alarms.', 520, 980, 42, 190, 5, 9, 40, 240, 30, 160, 4
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM car_thefts LIMIT 1);


/* Organised Crimes (MVP) */
CREATE TABLE IF NOT EXISTS organised_crimes (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NOT NULL,
  required_members TINYINT UNSIGNED NOT NULL DEFAULT 3,
  base_success_rate TINYINT UNSIGNED NOT NULL DEFAULT 55,
  min_total_reward INT NOT NULL DEFAULT 600,
  max_total_reward INT NOT NULL DEFAULT 1200,
  min_total_points INT NOT NULL DEFAULT 6,
  max_total_points INT NOT NULL DEFAULT 12,
  countdown_seconds INT NOT NULL DEFAULT 45,
  cooldown_seconds INT NOT NULL DEFAULT 180,
  jail_chance TINYINT UNSIGNED NOT NULL DEFAULT 35, -- on failure
  jail_seconds INT NOT NULL DEFAULT 180,
  hospital_chance TINYINT UNSIGNED NOT NULL DEFAULT 20, -- on failure
  hospital_seconds INT NOT NULL DEFAULT 120,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* One active "run" per organised crime at a time (pending/running/completed) */
CREATE TABLE IF NOT EXISTS organised_crime_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organised_crime_id SMALLINT UNSIGNED NOT NULL,
  status ENUM('pending','running','completed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  start_at TIMESTAMP NULL DEFAULT NULL, -- when countdown ends and outcome is resolved
  resolved_at TIMESTAMP NULL DEFAULT NULL,
  outcome ENUM('success','fail') NULL DEFAULT NULL,
  total_reward INT NULL DEFAULT NULL,
  total_points INT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_active (organised_crime_id, status),
  KEY idx_start_at (start_at),
  CONSTRAINT fk_ocr_crime FOREIGN KEY (organised_crime_id) REFERENCES organised_crimes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Members of a run */
CREATE TABLE IF NOT EXISTS organised_crime_members (
  run_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (run_id, user_id),
  KEY idx_user (user_id),
  CONSTRAINT fk_ocm_run FOREIGN KEY (run_id) REFERENCES organised_crime_runs(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Cooldowns per user per organised crime */
CREATE TABLE IF NOT EXISTS user_organised_crime_cooldowns (
  user_id BIGINT UNSIGNED NOT NULL,
  organised_crime_id SMALLINT UNSIGNED NOT NULL,
  last_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, organised_crime_id),
  CONSTRAINT fk_uocc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_uocc_crime FOREIGN KEY (organised_crime_id) REFERENCES organised_crimes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Seed organised crimes (only if empty) */
INSERT INTO organised_crimes
(name, description, required_members, base_success_rate, min_total_reward, max_total_reward, min_total_points, max_total_points, countdown_seconds, cooldown_seconds, jail_chance, jail_seconds, hospital_chance, hospital_seconds, sort_order)
SELECT * FROM (
  SELECT 'Warehouse Job', 'Hit a guarded warehouse with a small crew.', 3, 58, 750, 1400, 6, 12, 45, 180, 30, 160, 15, 110, 1 UNION ALL
  SELECT 'Armored Van', 'A risky ambush on an armored cash van.', 4, 52, 1200, 2200, 10, 18, 55, 240, 40, 220, 22, 150, 2 UNION ALL
  SELECT 'Bank Crew', 'Full crew bank run. High reward, high heat.', 5, 48, 2000, 3600, 16, 26, 70, 360, 45, 260, 28, 190, 3
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM organised_crimes LIMIT 1);

/* Players Online + Profiles */
ALTER TABLE users
  ADD COLUMN last_active TIMESTAMP NULL DEFAULT NULL,
  ADD COLUMN bio VARCHAR(255) NOT NULL DEFAULT '',
  ADD KEY idx_last_active (last_active);
