USE `silkhea2_jlfc`;

INSERT INTO `roles` (`name`, `description`) VALUES
('Super Admin', 'Full access to the JLFC platform.'),
('League Admin', 'Manages competitions, clubs, fixtures, results, discipline, protests, awards, media, and reports.'),
('Club Admin', 'Manages own club profile, players, staff, and views fixtures and results.'),
('Coach', 'Receives match preparation alerts and submits team formations for assigned club fixtures.'),
('Match Commissioner', 'Submits match reports and match results.'),
('Referee Coordinator', 'Assigns referees and updates officiating details.'),
('Finance Officer', 'Manages club payments, fines, appeal fees, and financial reports.'),
('Media Officer', 'Publishes news, gallery content, and match media.'),
('Viewer', 'Read-only backend access.')
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`);

INSERT INTO `permissions` (`permission_key`, `permission_name`, `module_name`) VALUES
('view_dashboard', 'View dashboard', 'dashboard'),
('manage_users', 'Manage users', 'users'),
('manage_roles', 'Manage roles', 'roles'),
('manage_permissions', 'Manage permissions', 'roles'),
('manage_seasons', 'Manage seasons', 'seasons'),
('manage_competitions', 'Manage competitions', 'competitions'),
('manage_clubs', 'Manage clubs', 'clubs'),
('approve_clubs', 'Approve clubs', 'clubs'),
('manage_players', 'Manage players', 'players'),
('approve_players', 'Approve players', 'players'),
('manage_player_licenses', 'Manage player licenses', 'licenses'),
('verify_player_licenses', 'Verify player licenses', 'licenses'),
('manage_club_staff', 'Manage club staff', 'staff'),
('manage_venues', 'Manage venues', 'venues'),
('manage_referees', 'Manage referees', 'referees'),
('assign_referees', 'Assign referees', 'referees'),
('manage_fixtures', 'Manage fixtures', 'fixtures'),
('manage_match_preparation', 'Manage match preparation', 'fixtures'),
('generate_fixtures', 'Generate fixtures', 'fixtures'),
('submit_results', 'Submit results', 'matches'),
('verify_results', 'Verify results', 'matches'),
('manage_table', 'Manage table', 'table'),
('recalculate_table', 'Recalculate table', 'table'),
('manage_statistics', 'Manage statistics', 'statistics'),
('manage_discipline', 'Manage discipline', 'discipline'),
('manage_suspensions', 'Manage suspensions', 'discipline'),
('manage_finance', 'Manage finance', 'finance'),
('record_payments', 'Record payments', 'finance'),
('manage_protests', 'Manage protests', 'protests'),
('decide_protests', 'Decide protests', 'protests'),
('manage_awards', 'Manage awards', 'awards'),
('manage_media', 'Manage media', 'media'),
('publish_news', 'Publish news', 'media'),
('manage_transfers', 'Manage transfers', 'transfers'),
('approve_transfers', 'Approve transfers', 'transfers'),
('view_reports', 'View reports', 'reports'),
('export_reports', 'Export reports', 'reports'),
('manage_settings', 'Manage settings', 'settings')
ON DUPLICATE KEY UPDATE
`permission_name` = VALUES(`permission_name`),
`module_name` = VALUES(`module_name`);

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.name = 'Super Admin'
  AND NOT EXISTS (
      SELECT 1
      FROM `role_permissions` rp
      WHERE rp.role_id = r.id
        AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN (
    'view_dashboard', 'manage_seasons', 'manage_competitions', 'manage_clubs', 'approve_clubs',
    'manage_players', 'approve_players', 'manage_player_licenses', 'verify_player_licenses', 'manage_club_staff', 'manage_venues', 'manage_referees',
  'assign_referees', 'manage_fixtures', 'manage_match_preparation', 'generate_fixtures', 'submit_results', 'verify_results',
    'manage_table', 'recalculate_table', 'manage_statistics', 'manage_discipline', 'manage_suspensions',
    'manage_protests', 'decide_protests', 'manage_awards', 'manage_media', 'publish_news',
    'manage_transfers', 'approve_transfers',
    'view_reports', 'export_reports'
)
WHERE r.name = 'League Admin'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN (
  'view_dashboard', 'manage_clubs', 'manage_players', 'manage_club_staff', 'manage_transfers', 'manage_match_preparation'
)
WHERE r.name = 'Club Admin'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'manage_match_preparation')
WHERE r.name = 'Coach'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'submit_results', 'verify_player_licenses')
WHERE r.name = 'Match Commissioner'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'manage_referees', 'assign_referees', 'manage_fixtures', 'verify_player_licenses')
WHERE r.name = 'Referee Coordinator'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'manage_finance', 'record_payments', 'view_reports', 'export_reports')
WHERE r.name = 'Finance Officer'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'manage_media', 'publish_news')
WHERE r.name = 'Media Officer'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id
FROM `roles` r
INNER JOIN `permissions` p ON p.permission_key IN ('view_dashboard', 'view_reports')
WHERE r.name = 'Viewer'
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO `system_settings` (`setting_key`, `setting_value`) VALUES
('league_name', 'JLFC - Jalingo Local Football Council'),
('league_short_name', 'JLFC'),
('organization_name', 'Jalingo Local Football Council'),
('contact_email', 'admin@jlfc.com'),
('contact_phone', '+2340000000000'),
('primary_color', 'dark green'),
('accent_color', 'gold'),
('season_mode', 'single'),
('points_win', '3'),
('points_draw', '1'),
('points_loss', '0'),
('yellow_card_suspension_threshold', '5'),
('red_card_suspension_default', '1'),
('protest_deadline_hours', '48'),
('require_payment_before_approval', '0'),
('enable_public_registration', '0'),
('enable_multiple_active_seasons', '0')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

INSERT INTO `clubs` (`name`, `short_name`, `license_status`, `payment_status`, `status`, `created_at`, `updated_at`) VALUES
('Super Stars FC', 'SSFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Tiki Taka FC', 'TTFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Black Scorpion FC', 'BSFC', 'approved', 'paid', 'active', NOW(), NOW()),
('SilkHeart FC', 'SH-FC', 'approved', 'paid', 'active', NOW(), NOW()),
('Arsenal Academy', 'AA', 'approved', 'paid', 'active', NOW(), NOW()),
('FC Takanabang', 'FCT', 'approved', 'paid', 'active', NOW(), NOW()),
('Golden Eaglet', 'GE', 'approved', 'paid', 'active', NOW(), NOW()),
('Alianze FC', 'AFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Barade FC', 'BFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Barade United', 'BU', 'approved', 'paid', 'active', NOW(), NOW()),
('Nunkai FC', 'NKFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Kona FC', 'KFC', 'approved', 'paid', 'active', NOW(), NOW()),
('SB United FC', 'SBUF', 'approved', 'paid', 'active', NOW(), NOW()),
('Ade Stars FC', 'ASFC', 'approved', 'paid', 'active', NOW(), NOW()),
('11 Bullet FC', '11B', 'approved', 'paid', 'active', NOW(), NOW()),
('Jalingo City FC', 'JCFC', 'approved', 'paid', 'active', NOW(), NOW()),
('Turaki City FC', 'TCFC', 'approved', 'paid', 'active', NOW(), NOW())
ON DUPLICATE KEY UPDATE
`short_name` = VALUES(`short_name`),
`license_status` = VALUES(`license_status`),
`payment_status` = VALUES(`payment_status`),
`status` = VALUES(`status`),
`updated_at` = NOW();

INSERT INTO `users` (`full_name`, `email`, `phone`, `password_hash`, `role_id`, `club_id`, `status`, `created_at`, `updated_at`)
SELECT
    'JLFC Super Administrator',
    'admin@jlfc.com',
    '+2340000000000',
    '$2y$10$VUHzXqRa66/kBfm6okdu2e7yesz1B0TYFrqYPA2.ZKCGkBZEOxh7W',
    r.id,
    NULL,
    'active',
    NOW(),
    NOW()
FROM `roles` r
WHERE r.name = 'Super Admin'
  AND NOT EXISTS (
      SELECT 1 FROM `users` u WHERE u.email = 'admin@jlfc.com'
  );
