-- Local SQLite database initialization for Spixer API
-- This file creates all necessary tables for local development and testing.

PRAGMA foreign_keys = ON;

--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
  `id` TEXT NOT NULL,
  `email` TEXT DEFAULT NULL,
  `username` TEXT DEFAULT NULL,
  `password_hash` TEXT,
  `email_verified` INTEGER NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE (`email`),
  UNIQUE (`username`)
);

--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `label` TEXT NOT NULL
);

--
-- Table structure for table `roles`
--
CREATE TABLE IF NOT EXISTS `roles` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT NOT NULL UNIQUE
);

--
-- Table structure for table `federations`
--
CREATE TABLE IF NOT EXISTS `federations` (
  `id` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

--
-- Table structure for table `clubs`
--
CREATE TABLE IF NOT EXISTS `clubs` (
  `id` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `federation_id` TEXT DEFAULT NULL,
  `created_by` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`federation_id`) REFERENCES `federations` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `events`
--
CREATE TABLE IF NOT EXISTS `events` (
  `id` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `label` TEXT DEFAULT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `organiser_id` TEXT DEFAULT NULL,
  `club_id` TEXT DEFAULT NULL,
  `PMR` INTEGER NOT NULL DEFAULT 0,
  `postal_code` TEXT NOT NULL,
  `address` TEXT DEFAULT NULL,
  `city` TEXT DEFAULT NULL,
  `region` TEXT DEFAULT NULL,
  `country` TEXT DEFAULT NULL,
  `oneplan_path` TEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`organiser_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL
);

--
-- Table structure for table `stages`
--
CREATE TABLE IF NOT EXISTS `stages` (
  `id` TEXT NOT NULL,
  `event_id` TEXT NOT NULL,
  `name` TEXT NOT NULL,
  `description` TEXT,
  `start_time` DATETIME NULL DEFAULT NULL,
  `end_time` DATETIME NULL DEFAULT NULL,
  `registration_end_time` DATETIME NULL DEFAULT NULL,
  `category_id` INTEGER DEFAULT NULL,
  `max_participants` INTEGER DEFAULT 100,
  `min_age` INTEGER DEFAULT NULL,
  `gpx_file_url` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` TEXT NOT NULL DEFAULT 'gpx',
  `lap_distance_meters` INTEGER DEFAULT NULL,
  `required_laps` INTEGER DEFAULT NULL,
  `total_distance_meters` INTEGER DEFAULT NULL,
  `bib_number_min` INTEGER DEFAULT NULL,
  `bib_number_max` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE SET NULL
);

--
-- Table structure for table `entity_images`
--
CREATE TABLE IF NOT EXISTS `entity_images` (
  `id` TEXT NOT NULL,
  `image_url` TEXT NOT NULL,
  `entity_id` TEXT NOT NULL,
  `entity_type` TEXT NOT NULL CHECK(`entity_type` IN ('event','club','stage')),
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

--
-- Table structure for table `club_memberships`
--
CREATE TABLE IF NOT EXISTS `club_memberships` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `club_id` TEXT NOT NULL,
  `role` TEXT NOT NULL DEFAULT 'pending' CHECK(`role` IN ('admin','member','pending')),
  `license_number` TEXT DEFAULT NULL,
  `joined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`user_id`, `club_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `user_roles`
--
CREATE TABLE IF NOT EXISTS `user_roles` (
  `user_id` TEXT NOT NULL,
  `role_id` INTEGER NOT NULL,
  `stage_id` TEXT,
  PRIMARY KEY (`user_id`,`role_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE SET NULL
);

--
-- Table structure for table `user_informations`
--
CREATE TABLE IF NOT EXISTS `user_informations` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `first_name` BLOB,
  `last_name` BLOB,
  `bio` BLOB,
  `avatar_url` TEXT,
  `location` BLOB,
  `birthdate` DATETIME NULL DEFAULT NULL,
  `is_premium_member` INTEGER NOT NULL DEFAULT 0,
  UNIQUE (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `user_favorite_categories`
--
CREATE TABLE IF NOT EXISTS `user_favorite_categories` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `category_id` INTEGER NOT NULL,
  UNIQUE (`user_id`,`category_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `registrations`
--
CREATE TABLE IF NOT EXISTS `registrations` (
  `id` TEXT NOT NULL,
  `user_id` TEXT NOT NULL,
  `stage_id` TEXT NOT NULL,
  `type` TEXT DEFAULT 'runner' CHECK (type IN ('runner')),
  `status` TEXT DEFAULT 'pending' CHECK(`status` IN ('pending','approved','rejected')),
  `bib_number` TEXT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `event_volunteers`
--
CREATE TABLE IF NOT EXISTS `event_volunteers` (
  `id` TEXT NOT NULL,
  `user_id` TEXT NOT NULL,
  `event_id` TEXT NOT NULL,
  `status` TEXT NOT NULL DEFAULT 'pending' CHECK(`status` IN ('pending','approved','rejected')),
  `role` TEXT DEFAULT NULL,
  `notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE (`user_id`,`event_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `rankings`
--
CREATE TABLE IF NOT EXISTS `rankings` (
  `id` TEXT NOT NULL,
  `stage_id` TEXT NOT NULL,
  `registration_id` TEXT NOT NULL,
  `rank_position` INTEGER DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `ranking_checkins`
--
CREATE TABLE IF NOT EXISTS `ranking_checkins` (
  `id` TEXT NOT NULL,
  `ranking_id` TEXT NOT NULL,
  `stage_id` TEXT NOT NULL,
  `checkin_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lap_number` INTEGER DEFAULT NULL,
  `latitude` REAL DEFAULT NULL,
  `longitude` REAL DEFAULT NULL,
  `recorded_by_user_id` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`recorded_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
);

--
-- Table structure for table `ranking_records`
--
CREATE TABLE IF NOT EXISTS `ranking_records` (
  `id` TEXT NOT NULL,
  `ranking_id` TEXT NOT NULL,
  `face_signature` TEXT,
  `ocr_label` TEXT,
  `latitude` REAL DEFAULT NULL,
  `longitude` REAL DEFAULT NULL,
  `source` TEXT DEFAULT 'ai',
  `captured_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `guest_profiles`
--
CREATE TABLE IF NOT EXISTS `guest_profiles` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `managed_by_id` TEXT NOT NULL,
  `guest_user_id` TEXT NOT NULL,
  `status` TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'unlinked')),
  UNIQUE (`guest_user_id`),
  FOREIGN KEY (`managed_by_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`guest_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `guest_claim_tokens`
--
CREATE TABLE IF NOT EXISTS `guest_claim_tokens` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `guest_user_id` TEXT NOT NULL,
  `email` TEXT NOT NULL,
  `token_hash` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  UNIQUE (`guest_user_id`),
  FOREIGN KEY (`guest_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `email_change_requests`
--
CREATE TABLE IF NOT EXISTS `email_change_requests` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `new_email` TEXT NOT NULL,
  `token_hash` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `payments`
--
CREATE TABLE IF NOT EXISTS `payments` (
  `id` TEXT NOT NULL,
  `user_id` TEXT NOT NULL,
  `amount` INTEGER NOT NULL,
  `currency` TEXT NOT NULL,
  `status` TEXT NOT NULL,
  `stripe_payment_intent_id` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `price`
--
CREATE TABLE IF NOT EXISTS `price` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT NOT NULL,
  `amount` INTEGER NOT NULL,
  `currency` TEXT NOT NULL
);

--
-- Table structure for table `newsletter_subscribers`
--
CREATE TABLE IF NOT EXISTS `newsletter_subscribers` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `email` TEXT NOT NULL,
  `subscribed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`email`)
);

--
-- Table structure for table `user_tokens`
--
CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `token_hash` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (`token_hash`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `user_refresh_tokens`
--
CREATE TABLE IF NOT EXISTS `user_refresh_tokens` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `user_id` TEXT NOT NULL,
  `token_hash` TEXT NOT NULL,
  `family` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  UNIQUE (`token_hash`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `stage_checkpoints`
--
CREATE TABLE IF NOT EXISTS `stage_checkpoints` (
    `id` TEXT NOT NULL,
    `stage_id` TEXT NOT NULL,
    `name` TEXT NOT NULL,
    `type` TEXT NOT NULL CHECK(type IN ('start', 'finish', 'lap', 'intermediate')),
    `latitude` REAL,
    `longitude` REAL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `checkpoint_assignments`
--
CREATE TABLE IF NOT EXISTS `checkpoint_assignments` (
    `id` TEXT NOT NULL,
    `checkpoint_id` TEXT NOT NULL,
    `volunteer_user_id` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE (`checkpoint_id`, `volunteer_user_id`),
    FOREIGN KEY (`checkpoint_id`) REFERENCES `stage_checkpoints` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`volunteer_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `stage_categories`
--
CREATE TABLE IF NOT EXISTS `stage_categories` (
  `id` TEXT NOT NULL,
  `label` TEXT NOT NULL,
  `group_name` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

--
-- Table structure for table `stage_category_assignments`
--
CREATE TABLE IF NOT EXISTS `stage_category_assignments` (
  `stage_id` TEXT NOT NULL,
  `category_id` TEXT NOT NULL,
  PRIMARY KEY (`stage_id`, `category_id`),
  FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `stage_categories` (`id`) ON DELETE CASCADE
);

--
-- Table structure for table `registration_category_assignments`
--
CREATE TABLE IF NOT EXISTS `registration_category_assignments` (
  `registration_id` TEXT NOT NULL,
  `category_id` TEXT NOT NULL,
  PRIMARY KEY (`registration_id`, `category_id`),
  FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `stage_categories` (`id`) ON DELETE CASCADE
);

--
-- Seed data
--
INSERT OR IGNORE INTO `category` (`id`, `label`) VALUES (1, 'Course à pied'), (2, 'Trail'), (3, 'VTT'), (4, 'Triathlon');
INSERT OR IGNORE INTO `roles` (`id`, `name`) VALUES (1, 'admin'), (2, 'organizer'), (3, 'runner'), (4, 'volunteer');
INSERT OR IGNORE INTO `stage_categories` (`id`, `label`, `group_name`) VALUES
('a1b2c3d4-0001-4000-8000-000000000001', 'Male', 'Gender'),
('a1b2c3d4-0001-4000-8000-000000000002', 'Female', 'Gender'),
('a1b2c3d4-0001-4000-8000-000000000003', 'Other', 'Gender'),
('a1b2c3d4-0002-4000-8000-000000000004', 'Elite', 'Skill'),
('a1b2c3d4-0002-4000-8000-000000000005', 'Amateur', 'Skill'),
('a1b2c3d4-0003-4000-8000-000000000006', 'U18', 'Age'),
('a1b2c3d4-0003-4000-8000-000000000007', '18-39', 'Age'),
('a1b2c3d4-0003-4000-8000-000000000008', '40+', 'Age');