CREATE TABLE seasons (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(20) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  label VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE season_category_rules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  season_id INT UNSIGNED NOT NULL,
  birth_year SMALLINT NOT NULL,
  sex ENUM('M','F') NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  UNIQUE KEY uq_rule (season_id,birth_year,sex),
  FOREIGN KEY (season_id) REFERENCES seasons(id),
  FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE players (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  last_name VARCHAR(120) NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  birth_date DATE NOT NULL,
  sex ENUM('M','F') NOT NULL,
  birth_country CHAR(5) NOT NULL,
  birth_postal_code VARCHAR(20) NOT NULL,
  birth_city VARCHAR(150) NOT NULL,
  email VARCHAR(190) NULL,
  phone_country CHAR(5) NULL,
  phone_e164 VARCHAR(30) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE addresses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  player_id BIGINT UNSIGNED NOT NULL,
  country CHAR(5) NOT NULL,
  postal_code VARCHAR(20) NOT NULL,
  city VARCHAR(150) NOT NULL,
  street VARCHAR(190) NOT NULL,
  house_number VARCHAR(30) NOT NULL,
  address_extra VARCHAR(120) NULL,
  validation_status ENUM('verified','unverified','manual') NOT NULL DEFAULT 'manual',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE affiliations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(40) NOT NULL UNIQUE,
  player_id BIGINT UNSIGNED NOT NULL,
  season_id INT UNSIGNED NOT NULL,
  locale CHAR(2) NOT NULL DEFAULT 'fr',
  declared_previous_club_status ENUM('yes','no','unknown') NOT NULL,
  declared_previous_club_country CHAR(5) NULL,
  declared_previous_club_name VARCHAR(190) NULL,
  confirmed_previous_club_country CHAR(5) NULL,
  confirmed_previous_club_name VARCHAR(190) NULL,
  calculated_category_code VARCHAR(10) NULL,
  is_minor TINYINT(1) NOT NULL,
  dossier_status ENUM('new','to_review','documents_missing','complete','abandoned','refused') NOT NULL DEFAULT 'new',
  affiliation_type ENUM('to_determine','new_affiliation','national_transfer','international_transfer') NOT NULL DEFAULT 'to_determine',
  ffa_status ENUM('not_processed','to_encode','encoded_signature_pending','validated','done','error') NOT NULL DEFAULT 'not_processed',
  psd_status ENUM('not_processed','to_encode','encoded','error') NOT NULL DEFAULT 'not_processed',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (player_id) REFERENCES players(id),
  FOREIGN KEY (season_id) REFERENCES seasons(id),
  INDEX idx_status (dossier_status,ffa_status,psd_status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE guardians (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL,
  relationship ENUM('father','mother','legal_guardian','other') NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone_country CHAR(5) NOT NULL,
  phone_e164 VARCHAR(30) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE,
  INDEX idx_guardian_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE health_safety (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL UNIQUE,
  may_leave_alone TINYINT(1) NULL,
  health_info_encrypted TEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE consents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL,
  consent_type VARCHAR(50) NOT NULL,
  consent_value TINYINT(1) NOT NULL,
  text_version VARCHAR(30) NOT NULL,
  consented_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  side VARCHAR(30) NOT NULL DEFAULT 'document',
  original_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  sha256 CHAR(64) NOT NULL,
  status ENUM('received','verified','rejected','archived') NOT NULL DEFAULT 'received',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE,
  INDEX idx_docs (affiliation_id,document_type,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE upload_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  role ENUM('admin','manager','read_only') NOT NULL DEFAULT 'manager',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE email_templates (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(80) NOT NULL,
  locale CHAR(2) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body_html MEDIUMTEXT NOT NULL,
  cc_emails TEXT NULL,
  bcc_emails TEXT NULL,
  copy_coach TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_mail_tpl (code,locale)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  affiliation_id BIGINT UNSIGNED NOT NULL,
  template_code VARCHAR(80) NOT NULL,
  recipients_to TEXT NOT NULL,
  recipients_cc TEXT NULL,
  recipients_bcc TEXT NULL,
  subject VARCHAR(255) NOT NULL,
  status ENUM('sent','error') NOT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  `key` VARCHAR(100) PRIMARY KEY,
  `value` MEDIUMTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT UNSIGNED NULL,
  affiliation_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  details MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE SET NULL,
  FOREIGN KEY (affiliation_id) REFERENCES affiliations(id) ON DELETE SET NULL,
  INDEX idx_audit_aff (affiliation_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO seasons(label,starts_on,ends_on,is_active) VALUES ('2026-2027','2026-07-01','2027-06-30',1);

INSERT INTO categories(code,label) VALUES
('U6','U6'),('U7','U7'),('U8','U8'),('U9','U9'),('U10','U10'),('U11','U11'),('U12','U12'),('U13','U13'),('U14','U14'),('U15','U15'),('U16','U16'),('U17','U17'),('U19','U19'),('SEN','Seniors');

SET @season=(SELECT id FROM seasons WHERE label='2026-2027' LIMIT 1);

-- Exemple de règles 2026-2027. À vérifier/adapter dans l'admin avant mise en production.
INSERT INTO season_category_rules(season_id,birth_year,sex,category_id)
SELECT @season,2016,'M',id FROM categories WHERE code='U11';
INSERT INTO season_category_rules(season_id,birth_year,sex,category_id)
SELECT @season,2016,'F',id FROM categories WHERE code='U10';

INSERT INTO settings(`key`,`value`) VALUES
('default_cc',''),
('default_bcc',''),
('reminder_enabled','1'),
('reminder_day_1','5'),
('reminder_day_2','10');

INSERT INTO email_templates(code,locale,subject,body_html,cc_emails,bcc_emails,copy_coach,is_active) VALUES
('request_received','fr','Demande d''affiliation reçue - {{player_firstname}} {{player_lastname}}',
'<p>Bonjour,</p><p>Nous avons bien reçu la demande d''affiliation de <strong>{{player_firstname}} {{player_lastname}}</strong>.</p><p>Référence : <strong>{{affiliation_number}}</strong><br>Catégorie théorique : <strong>{{category}}</strong></p><p>Les données vont être vérifiées et traitées par le club.</p><p>{{missing_documents}}</p><p>Si des documents doivent encore être transmis, vous pouvez utiliser ce lien sécurisé :<br><a href="{{documents_link}}">{{documents_link}}</a></p><p>R.E.S. Aubange</p>',
'','',0,1),

('ffa_new','fr','Affiliation encodée à la fédération - {{player_firstname}} {{player_lastname}}',
'<p>Bonjour,</p><p>L''affiliation est encodée à la fédération. Vous avez dû recevoir un e-mail de la fédération pour la signer électroniquement.</p><p>Il faut cliquer sur le lien, cocher les cases, demander le code par SMS et le saisir pour valider.</p><p>Si vous ne voyez pas cet e-mail, vérifiez dans vos spams.</p><p>R.E.S. Aubange</p>',
'','',1,1),

('ffa_transfer','fr','Transfert encodé à la fédération - {{player_firstname}} {{player_lastname}}',
'<p>Bonjour,</p><p>Le transfert est encodé. Vous devez le signer avec l''e-mail reçu de la fédération et ensuite l''ancien club devra également le signer.</p><p>Il faut cliquer sur le lien, cocher les cases, demander le code par SMS et le saisir pour valider.</p><p>Si vous ne voyez pas cet e-mail, vérifiez dans vos spams.</p><p>R.E.S. Aubange</p>',
'','',1,1),

('psd_encoded','fr','Pro Soccer Data - {{player_firstname}} {{player_lastname}}',
'<p>Bonjour,</p><p>{{player_firstname}} {{player_lastname}} a été ajouté dans Pro Soccer Data. Vous pourrez y consulter le calendrier des activités et répondre aux convocations.</p><p>R.E.S. Aubange</p>',
'','',0,1),

('finalized','fr','Affiliation finalisée - {{player_firstname}} {{player_lastname}}',
'<p>Bonjour,</p><p>L''affiliation de <strong>{{player_firstname}} {{player_lastname}}</strong> est maintenant finalisée.</p><p>Bienvenue à la R.E.S. Aubange !</p>',
'','',0,1);