USE `silkhea2_jlfc`;

CREATE TABLE IF NOT EXISTS `transfer_windows` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `season_id` BIGINT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(150) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `status` ENUM('draft', 'open', 'closed') NOT NULL DEFAULT 'draft',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_windows_season_id` (`season_id`),
    CONSTRAINT `fk_transfer_windows_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `selling_club_id` BIGINT UNSIGNED NOT NULL,
    `buying_club_id` BIGINT UNSIGNED NOT NULL,
    `initiated_by_user_id` BIGINT UNSIGNED NOT NULL,
    `window_id` BIGINT UNSIGNED DEFAULT NULL,
    `current_stage` ENUM('market', 'interest_registered', 'club_review', 'negotiation', 'verbal_agreement', 'awaiting_club_approval', 'awaiting_league_approval', 'approved', 'medical', 'completed', 'rejected', 'cancelled') NOT NULL DEFAULT 'market',
    `market_visibility` ENUM('open', 'restricted', 'closed') NOT NULL DEFAULT 'open',
    `interest_message` TEXT DEFAULT NULL,
    `proposed_release_date` DATE DEFAULT NULL,
    `proposed_fee` DECIMAL(12,2) DEFAULT NULL,
    `agreed_fee` DECIMAL(12,2) DEFAULT NULL,
    `contract_years` INT UNSIGNED DEFAULT NULL,
    `medical_status` ENUM('not_started', 'pending', 'completed', 'failed') NOT NULL DEFAULT 'not_started',
    `effective_date` DATE DEFAULT NULL,
    `completed_at` DATETIME DEFAULT NULL,
    `rejection_reason` 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`),
    KEY `idx_transfers_player_id` (`player_id`),
    KEY `idx_transfers_selling_club_id` (`selling_club_id`),
    KEY `idx_transfers_buying_club_id` (`buying_club_id`),
    KEY `idx_transfers_initiated_by_user_id` (`initiated_by_user_id`),
    KEY `idx_transfers_window_id` (`window_id`),
    CONSTRAINT `chk_transfers_clubs_different` CHECK (`selling_club_id` <> `buying_club_id`),
    CONSTRAINT `fk_transfers_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_selling_club_id` FOREIGN KEY (`selling_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_buying_club_id` FOREIGN KEY (`buying_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_initiated_by_user_id` FOREIGN KEY (`initiated_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_window_id` FOREIGN KEY (`window_id`) REFERENCES `transfer_windows` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfer_offers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `offered_by_club_id` BIGINT UNSIGNED NOT NULL,
    `offered_by_user_id` BIGINT UNSIGNED NOT NULL,
    `offered_fee` DECIMAL(12,2) DEFAULT NULL,
    `contract_years` INT UNSIGNED DEFAULT NULL,
    `release_date` DATE DEFAULT NULL,
    `status` ENUM('proposed', 'accepted', 'rejected', 'withdrawn') NOT NULL DEFAULT 'proposed',
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_offers_transfer_id` (`transfer_id`),
    KEY `idx_transfer_offers_offered_by_club_id` (`offered_by_club_id`),
    KEY `idx_transfer_offers_offered_by_user_id` (`offered_by_user_id`),
    CONSTRAINT `fk_transfer_offers_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_offers_offered_by_club_id` FOREIGN KEY (`offered_by_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_offers_offered_by_user_id` FOREIGN KEY (`offered_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfer_approvals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `approval_type` ENUM('selling_club', 'buying_club', 'league') NOT NULL,
    `status` ENUM('pending', 'accepted', 'rejected') NOT NULL DEFAULT 'pending',
    `acted_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `acted_at` DATETIME DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_transfer_approval_scope` (`transfer_id`, `approval_type`),
    KEY `idx_transfer_approvals_acted_by_user_id` (`acted_by_user_id`),
    CONSTRAINT `fk_transfer_approvals_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_approvals_acted_by_user_id` FOREIGN KEY (`acted_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfer_timeline_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `event_type` VARCHAR(100) NOT NULL,
    `headline` VARCHAR(220) NOT NULL,
    `body` TEXT DEFAULT NULL,
    `publish_news` TINYINT(1) NOT NULL DEFAULT 0,
    `news_post_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_timeline_events_transfer_id` (`transfer_id`),
    KEY `idx_transfer_timeline_events_news_post_id` (`news_post_id`),
    KEY `idx_transfer_timeline_events_created_by_user_id` (`created_by_user_id`),
    CONSTRAINT `fk_transfer_timeline_events_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_timeline_events_news_post_id` FOREIGN KEY (`news_post_id`) REFERENCES `news_posts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_timeline_events_created_by_user_id` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transfer_notifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `recipient_user_id` BIGINT UNSIGNED NOT NULL,
    `notification_type` VARCHAR(100) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `body` TEXT DEFAULT NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `read_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_notifications_transfer_id` (`transfer_id`),
    KEY `idx_transfer_notifications_recipient_user_id` (`recipient_user_id`),
    CONSTRAINT `fk_transfer_notifications_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_notifications_recipient_user_id` FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `permissions` (`permission_key`, `permission_name`, `module_name`) VALUES
('manage_transfers', 'Manage transfers', 'transfers'),
('approve_transfers', 'Approve transfers', 'transfers')
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
INNER JOIN `permissions` p ON p.permission_key IN ('manage_transfers', 'approve_transfers')
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 ('manage_transfers')
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
  );