-- ============================================================
-- NROI SYSTEM — SAFE DEPLOYMENT MIGRATION SCRIPT
-- Run this on the cPanel phpMyAdmin for database: nroinet_db
-- 
-- All statements use IF NOT EXISTS / IF EXISTS guards so they 
-- are COMPLETELY SAFE to run even if some columns/tables already
-- exist — NO existing data will be modified or deleted.
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ============================================================
-- TABLE: disciplines
-- (May already exist — CREATE IF NOT EXISTS is safe)
-- ============================================================
CREATE TABLE IF NOT EXISTS `disciplines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Seed default disciplines only if table is empty
INSERT IGNORE INTO `disciplines` (`name`) VALUES
  ('Handgun'),
  ('Shotgun'),
  ('Rifle'),
  ('PCC'),
  ('Production Optics'),
  ('Standard'),
  ('Classic'),
  ('Open'),
  ('Bad Record');

-- ============================================================
-- TABLE: ranks
-- ============================================================
CREATE TABLE IF NOT EXISTS `ranks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rank_name` varchar(100) NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rank_name` (`rank_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: areas
-- ============================================================
CREATE TABLE IF NOT EXISTS `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `area_name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `area_name` (`area_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: forms
-- ============================================================
CREATE TABLE IF NOT EXISTS `forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: form_attachments
-- ============================================================
CREATE TABLE IF NOT EXISTS `form_attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` enum('pdf','image') NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `form_id` (`form_id`),
  CONSTRAINT `form_attachments_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: reference_match_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `reference_match_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: reference_match_roles
-- ============================================================
CREATE TABLE IF NOT EXISTS `reference_match_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: reference_disqualification
-- ============================================================
CREATE TABLE IF NOT EXISTS `reference_disqualification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: reference_procedural
-- ============================================================
CREATE TABLE IF NOT EXISTS `reference_procedural` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: email_change_requests
-- ============================================================
CREATE TABLE IF NOT EXISTS `email_change_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `user_type` enum('regular','admin') NOT NULL,
  `old_email` varchar(255) NOT NULL,
  `token` varchar(100) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: removed_accounts
-- ============================================================
CREATE TABLE IF NOT EXISTS `removed_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `removal_type` enum('temporary','permanent') NOT NULL DEFAULT 'temporary',
  `signature` varchar(255) DEFAULT NULL,
  `removed_by_admin_id` int(11) DEFAULT NULL,
  `removed_by_user_id` int(11) DEFAULT NULL,
  `removed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `removed_accounts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: yearly_points_overrides
-- ============================================================
CREATE TABLE IF NOT EXISTS `yearly_points_overrides` (
  `record_year` int(11) NOT NULL,
  `overall_total` int(11) NOT NULL DEFAULT 0,
  `updated_by_role` varchar(10) DEFAULT NULL,
  `updated_by_admin_id` int(11) DEFAULT NULL,
  `updated_by_user_id` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`record_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: admin_password_resets
-- ============================================================
CREATE TABLE IF NOT EXISTS `admin_password_resets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `otp` varchar(6) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  CONSTRAINT `admin_password_resets_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: notification_messages (update FK if needed)
-- ============================================================
CREATE TABLE IF NOT EXISTS `notification_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `from_user_id` int(11) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `can_reply` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `from_user_id` (`from_user_id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `notification_messages_ibfk_1` FOREIGN KEY (`from_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `notification_messages_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `notification_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: notification_recipients
-- ============================================================
CREATE TABLE IF NOT EXISTS `notification_recipients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notification_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_notif_recipient` (`notification_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notification_recipients_ibfk_1` FOREIGN KEY (`notification_id`) REFERENCES `notification_messages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `notification_recipients_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: manual_match_points
-- ============================================================
CREATE TABLE IF NOT EXISTS `manual_match_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `recorded_by_user_id` int(11) DEFAULT NULL,
  `recorded_by_admin_id` int(11) DEFAULT NULL,
  `match_name` varchar(255) NOT NULL,
  `discipline` varchar(100) DEFAULT 'Handgun',
  `year` int(11) NOT NULL,
  `month` int(11) DEFAULT NULL,
  `level` varchar(50) NOT NULL,
  `role` varchar(100) NOT NULL,
  `points` decimal(10,2) NOT NULL,
  `location` varchar(255) DEFAULT '---',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_attachments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(255) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `match_id` (`match_id`),
  CONSTRAINT `match_attachments_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `match_schedules` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_chrono_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_chrono_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_chrono` (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_deputy_range_master_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_deputy_range_master_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_user_deputy_range_master` (`match_id`,`submitted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_equipment_check_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_equipment_check_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_user_equipment_check` (`match_id`,`submitted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_match_director_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_match_director_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_match_director` (`match_id`,`submitted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_quarter_master_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_quarter_master_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_user_quarter_master` (`match_id`,`submitted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_range_master_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_range_master_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_user_range_master` (`match_id`,`submitted_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_states_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_states_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `states_number` varchar(100) NOT NULL,
  `lro_name` varchar(255) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL COMMENT 'Path to RM signature image',
  `submitted_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_states` (`match_id`),
  KEY `submitted_by` (`submitted_by`),
  CONSTRAINT `match_states_assignments_ibfk_1` FOREIGN KEY (`match_id`) REFERENCES `match_schedules` (`id`) ON DELETE CASCADE,
  CONSTRAINT `match_states_assignments_ibfk_2` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: match_stats_assignments
-- ============================================================
CREATE TABLE IF NOT EXISTS `match_stats_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `match_id` int(11) NOT NULL,
  `officer_name` varchar(255) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rm_signature` varchar(255) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_match_stats` (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- TABLE: activity_logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference_type` varchar(50) NOT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `status` varchar(50) NOT NULL,
  `performed_by_role` varchar(20) NOT NULL,
  `performed_by_user_id` int(11) DEFAULT NULL,
  `performed_by_admin_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_performed_by` (`performed_by_role`,`performed_by_user_id`,`performed_by_admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- COLUMN ADDITIONS — users table
-- Each uses a stored procedure trick to safely add only if missing
-- ============================================================

-- users.username
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `username` varchar(50) DEFAULT NULL;

-- users.extension
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `extension` varchar(10) DEFAULT NULL;

-- users.middle_name
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `middle_name` varchar(100) DEFAULT NULL;

-- users.nroi_id
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `nroi_id` varchar(20) DEFAULT NULL;

-- users.ppsa_id
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `ppsa_id` varchar(50) DEFAULT NULL;

-- users.profile_photo
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `profile_photo` varchar(255) DEFAULT NULL;

-- users.area
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `area` varchar(100) DEFAULT NULL;

-- users.rank
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `rank` varchar(100) DEFAULT NULL;

-- users.region
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `region` varchar(100) DEFAULT NULL;

-- users.province
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `province` varchar(100) DEFAULT NULL;

-- users.city_municipality
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `city_municipality` varchar(100) DEFAULT NULL;

-- users.barangay
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `barangay` varchar(100) DEFAULT NULL;

-- users.zipcode
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `zipcode` varchar(10) DEFAULT NULL;

-- users.street
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `street` text DEFAULT NULL;

-- users.facebook_link
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `facebook_link` varchar(255) DEFAULT NULL;

-- users.viber_number
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `viber_number` varchar(20) DEFAULT NULL;

-- users.telegram_number
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `telegram_number` varchar(20) DEFAULT NULL;

-- users.emergency_contact_name
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `emergency_contact_name` varchar(100) DEFAULT NULL;

-- users.emergency_contact_number
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `emergency_contact_number` varchar(20) DEFAULT NULL;

-- users.position
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `position` varchar(100) DEFAULT NULL;

-- users.age
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `age` int(11) DEFAULT NULL;

-- users.is_active_nroi_member
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `is_active_nroi_member` tinyint(1) DEFAULT 0;

-- users.is_email_verified
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `is_email_verified` tinyint(1) DEFAULT 0;

-- users.verification_token
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `verification_token` varchar(100) DEFAULT NULL;

-- users.email_verified_at
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `email_verified_at` timestamp NULL DEFAULT NULL;

-- users.is_email_confirmed
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `is_email_confirmed` tinyint(1) DEFAULT 0;

-- users.email_confirmation_token
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `email_confirmation_token` varchar(255) DEFAULT NULL;

-- users.is_first_login
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `is_first_login` tinyint(1) DEFAULT 0;

-- users.match_assignment
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `match_assignment` varchar(255) DEFAULT NULL;

-- users.match_role
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `match_role` varchar(255) DEFAULT NULL;

-- Safely add unique indexes only if they don't already exist
-- (ALTER IGNORE does NOT suppress duplicate key name errors in modern MySQL/MariaDB)
DROP PROCEDURE IF EXISTS _nroi_add_unique_index;
DELIMITER //
CREATE PROCEDURE _nroi_add_unique_index(
    IN tbl VARCHAR(128),
    IN idx VARCHAR(128),
    IN col VARCHAR(128)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name   = tbl
          AND index_name   = idx
    ) THEN
        SET @_sql = CONCAT('ALTER TABLE `', tbl, '` ADD UNIQUE KEY `', idx, '` (`', col, '`)');
        PREPARE _stmt FROM @_sql;
        EXECUTE _stmt;
        DEALLOCATE PREPARE _stmt;
    END IF;
END //
DELIMITER ;

CALL _nroi_add_unique_index('users', 'username', 'username');
CALL _nroi_add_unique_index('users', 'nroi_id',  'nroi_id');

DROP PROCEDURE IF EXISTS _nroi_add_unique_index;

-- ============================================================
-- COLUMN ADDITIONS — admins table
-- ============================================================
ALTER TABLE `admins`
  ADD COLUMN IF NOT EXISTS `first_name` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `last_name` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `email` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `phone_number` varchar(20) DEFAULT NULL;

-- ============================================================
-- COLUMN ADDITIONS — match_schedules table
-- ============================================================
ALTER TABLE `match_schedules`
  ADD COLUMN IF NOT EXISTS `ro_limit` int(11) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `rm_name` varchar(255) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `rm_id` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `created_by` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `is_retroactive` tinyint(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `creator_area` varchar(100) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `is_local` tinyint(1) DEFAULT 0;

-- ============================================================
-- COLUMN ADDITIONS — match_significations table
-- ============================================================
ALTER TABLE `match_significations`
  ADD COLUMN IF NOT EXISTS `officer_remarks` text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `withdrawal_reason` text DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reviewed_by` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `reviewed_at` timestamp NULL DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `is_extended` tinyint(1) DEFAULT 0,
  ADD COLUMN IF NOT EXISTS `is_rm_enabled` tinyint(1) DEFAULT 0;

-- Update enum if it's missing newer values (safe — only adds if not present)
-- This modifies the enum to add new statuses. Existing values are preserved.
ALTER TABLE `match_significations`
  MODIFY COLUMN `status` enum('pending','approved','declined','standby','withdrawal_requested','withdrawn') DEFAULT 'pending';

-- ============================================================
-- COLUMN ADDITIONS — match_points table
-- ============================================================
-- match_points.rm_signature may already exist as TEXT type — this ensures it's longtext
ALTER TABLE `match_points`
  MODIFY COLUMN IF EXISTS `rm_signature` longtext NOT NULL;

-- ============================================================
-- COLUMN ADDITIONS — manual_match_points table
-- ============================================================
ALTER TABLE `manual_match_points`
  ADD COLUMN IF NOT EXISTS `recorded_by_user_id` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `recorded_by_admin_id` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `discipline` varchar(100) DEFAULT 'Handgun',
  ADD COLUMN IF NOT EXISTS `month` int(11) DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS `location` varchar(255) DEFAULT '---';

-- ============================================================
-- COLUMN ADDITIONS — password_resets table
-- ============================================================
ALTER TABLE `password_resets`
  ADD COLUMN IF NOT EXISTS `otp` varchar(6) NOT NULL DEFAULT '' AFTER `email`,
  ADD COLUMN IF NOT EXISTS `expires_at` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' AFTER `otp`;

-- ============================================================
-- COLUMN ADDITIONS — ranks table
-- ============================================================
ALTER TABLE `ranks`
  ADD COLUMN IF NOT EXISTS `sort_order` int(11) DEFAULT 0;

-- ============================================================
-- INDEX ADDITIONS — activity_logs
-- ============================================================
DROP PROCEDURE IF EXISTS _nroi_add_index;
DELIMITER //
CREATE PROCEDURE _nroi_add_index(
    IN tbl VARCHAR(128),
    IN idx VARCHAR(128),
    IN cols VARCHAR(512)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name   = tbl
          AND index_name   = idx
    ) THEN
        SET @_sql = CONCAT('ALTER TABLE `', tbl, '` ADD KEY `', idx, '` (', cols, ')');
        PREPARE _stmt FROM @_sql;
        EXECUTE _stmt;
        DEALLOCATE PREPARE _stmt;
    END IF;
END //
DELIMITER ;

CALL _nroi_add_index('activity_logs', 'idx_created_at',  '`created_at`');
CALL _nroi_add_index('activity_logs', 'idx_performed_by', '`performed_by_role`,`performed_by_user_id`,`performed_by_admin_id`');

DROP PROCEDURE IF EXISTS _nroi_add_index;

-- ============================================================
-- COLUMN ADDITIONS — notification_messages table
-- ============================================================
ALTER TABLE `notification_messages`
  ADD COLUMN IF NOT EXISTS `can_reply` tinyint(1) DEFAULT 1;

-- ============================================================
-- Done
-- ============================================================
SET foreign_key_checks = 1;

SELECT 'Migration complete. All new tables and columns have been safely added.' AS Result;
