-- May Cash Franchise Questionnaire — Database Schema
-- MySQL 8 / MariaDB 10.5+
--
-- Create the database (adjust the name to your Hostinger DB):
--   CREATE DATABASE IF NOT EXISTS maycash_franchise DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--   USE maycash_franchise;

-- ============================================================
-- Table: submissions
-- ============================================================
CREATE TABLE IF NOT EXISTS submissions (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name               VARCHAR(120)  NOT NULL,
    phone                   VARCHAR(30)   NOT NULL,
    email                   VARCHAR(160)  DEFAULT NULL,
    city                    VARCHAR(80)   NOT NULL,
    main_activity           VARCHAR(200)  NOT NULL,
    has_commercial_location ENUM('oui','non') NOT NULL,
    city_second             VARCHAR(80)   DEFAULT NULL,
    -- Attribution (auto-captured)
    utm_source              VARCHAR(80)   DEFAULT NULL,
    utm_medium              VARCHAR(80)   DEFAULT NULL,
    utm_campaign            VARCHAR(120)  DEFAULT NULL,
    utm_content             VARCHAR(120)  DEFAULT NULL,
    utm_term                VARCHAR(120)  DEFAULT NULL,
    fbclid                  VARCHAR(255)  DEFAULT NULL,
    ttclid                  VARCHAR(255)  DEFAULT NULL,
    referrer                VARCHAR(500)  DEFAULT NULL,
    -- Manager-managed fields
    contacted               ENUM('non','oui') NOT NULL DEFAULT 'non',
    rdv_at                  DATE          DEFAULT NULL,
    comment                 TEXT          DEFAULT NULL,
    is_gold                 TINYINT(1)    NOT NULL DEFAULT 0,
    -- Tech metadata
    ip_address              VARCHAR(45)   DEFAULT NULL,
    user_agent              VARCHAR(255)  DEFAULT NULL,
    created_at              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_city        (city),
    INDEX idx_created     (created_at),
    INDEX idx_activity    (main_activity),
    INDEX idx_has_local   (has_commercial_location),
    INDEX idx_contacted   (contacted),
    INDEX idx_utm_source  (utm_source),
    INDEX idx_gold        (is_gold)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: admins
-- ============================================================
CREATE TABLE IF NOT EXISTS admins (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username       VARCHAR(60)   NOT NULL UNIQUE,
    password_hash  VARCHAR(255)  NOT NULL,
    email          VARCHAR(120)  DEFAULT NULL,
    last_login     TIMESTAMP     NULL DEFAULT NULL,
    created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Table: login_attempts  (basic brute-force throttle)
-- ============================================================
CREATE TABLE IF NOT EXISTS login_attempts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip_address  VARCHAR(45) NOT NULL,
    username    VARCHAR(60) DEFAULT NULL,
    success     TINYINT(1)  NOT NULL DEFAULT 0,
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Seed: default admin user
-- Username:  admin
-- Password:  ChangeMe2026!
-- ⚠️  IMPORTANT: log in and change this immediately, or generate
--     a new hash with:  php -r "echo password_hash('your-password', PASSWORD_BCRYPT);"
-- ============================================================
INSERT INTO admins (username, password_hash, email)
VALUES ('admin', '$2y$12$6RmSk9K0Z7WxKZ8FJ1rQ1.v1tcMvPGY9vNxQoJsmz0ZnMDGvG0h4e', 'admin@maycash.ma')
ON DUPLICATE KEY UPDATE username = username;