-- 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: 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;
