-- =============================================================================
-- ASA PLATFORM - Database Schema & Seed Data (MySQL 8.4 LTS)
-- Operator: GRANIX
-- =============================================================================

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- Note: this schema does NOT create/select a database on purpose, so it can be
-- imported into whatever database the host provides (cPanel) or into the
-- database selected by docker-compose (MYSQL_DATABASE). Select your DB first.

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- 1. Table: users
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(191) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('ADMIN', 'MODERATOR', 'PROVIDER', 'CLIENT') NOT NULL DEFAULT 'CLIENT',
    `full_name` VARCHAR(191) NOT NULL,
    `phone` VARCHAR(50) NULL,
    `address` VARCHAR(255) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_users_role` (`role`),
    INDEX `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2. Table: categories (6 Catégories Réelles à Madagascar)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `slug` VARCHAR(150) NOT NULL UNIQUE,
    `description` TEXT NULL,
    `icon` VARCHAR(60) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_categories_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 3. Table: provider_profiles
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `provider_profiles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
    `category_id` BIGINT UNSIGNED NULL,
    `bio` TEXT NULL,
    `address` VARCHAR(255) NULL,
    `id_card_number` VARCHAR(100) NULL,
    `rate_ariary` INT UNSIGNED NULL DEFAULT 0,
    `kyc_status` ENUM('PENDING', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `kyc_document_url` VARCHAR(255) NULL,
    `kyc_notes` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_provider_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_provider_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
    INDEX `idx_provider_kyc_status` (`kyc_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 4. Table: interview_slots
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `interview_slots` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `moderator_id` BIGINT UNSIGNED NOT NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NOT NULL,
    `is_available` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_slot_moderator` FOREIGN KEY (`moderator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_slots_availability` (`is_available`, `start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 5. Table: interviews
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `interviews` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `slot_id` BIGINT UNSIGNED NOT NULL UNIQUE,
    `provider_id` BIGINT UNSIGNED NOT NULL,
    `status` ENUM('SCHEDULED', 'PASSED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'SCHEDULED',
    `notes` TEXT NULL,
    `conducted_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_interview_slot` FOREIGN KEY (`slot_id`) REFERENCES `interview_slots` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_interview_provider` FOREIGN KEY (`provider_id`) REFERENCES `provider_profiles` (`id`) ON DELETE CASCADE,
    INDEX `idx_interview_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 6. Table: auth_tokens (Mobile Bearer Authentication)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `auth_tokens` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `token_hash` VARCHAR(64) NOT NULL UNIQUE,
    `expires_at` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_token_lookup` (`token_hash`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 7. Table: login_attempts (Brute-force protection for backoffice)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ip` VARCHAR(45) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `attempted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_attempts_ip_email` (`ip`, `email`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 8. Table: device_keys (Canal chiffré applicatif, une clé par téléphone)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `device_keys` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `device_id` CHAR(32) NOT NULL UNIQUE,
    `public_key` VARCHAR(64) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen_at` DATETIME NULL,
    `revoked_at` DATETIME NULL,
    INDEX `idx_device_last_seen` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- SEED DATA (UTF-8)
-- =============================================================================

SET NAMES utf8mb4;

-- Demo users are intentionally NOT seeded here: committing known credentials
-- (and their bcrypt hashes) is a security risk. Local dev data lives in
-- seed_local.sql (LOCAL DEV ONLY). Production admins are created with
-- bin/create-admin.php. See README -> "Sécurité du Backoffice".

-- Seed 6 Core Malagasy Service Categories (Strict UTF-8)
INSERT INTO `categories` (`id`, `name`, `slug`, `description`, `icon`, `is_active`)
VALUES
(1, 'Plomberie & Électricité', 'plomberie-electricite', 'Dépannage plomberie, fuites d\'eau, installations électriques et mises aux normes.', 'wrench', 1),
(2, 'Mécanique à domicile', 'mecanique-domicile', 'Réparation, vidange, électricité et maintenance auto/moto directement à domicile.', 'tool', 1),
(3, 'Coiffure & Beauté', 'coiffure-beaute', 'Coiffure pour hommes et femmes, tresses africaines, maquillage et soins du visage.', 'scissors', 1),
(4, 'Nounous', 'nounous', 'Garde d\'enfants qualifiée et de confiance à domicile, à temps partiel ou complet.', 'heart', 1),
(5, 'Photographes', 'photographes', 'Couverture événementielle, mariages, shootings studio, portraits et vidéo.', 'camera', 1),
(6, 'Aide ménagère', 'aide-menagere', 'Ménage régulier, grand nettoyage, repassage et entretien complet du logement.', 'sparkles', 1)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `slug` = VALUES(`slug`), `description` = VALUES(`description`);

-- Demo provider profile + interview slots live in seed_local.sql (LOCAL DEV
-- ONLY). They are deliberately excluded from production schema imports.