-- Run this in phpMyAdmin or your MySQL client
USE flex_market;

-- Drop existing tables if updating
DROP TABLE IF EXISTS registration_attempts;
DROP TABLE IF EXISTS allowed_zones;

-- Table: allowed_zones
CREATE TABLE allowed_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_name VARCHAR(100) NOT NULL,
    zone_polygon TEXT NOT NULL COMMENT 'JSON array of lat/lng coordinates',
    is_active TINYINT(1) DEFAULT 1,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: registration_attempts
CREATE TABLE registration_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mobile VARCHAR(15) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    country VARCHAR(100),
    state VARCHAR(100),
    city VARCHAR(100),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    is_allowed TINYINT(1) DEFAULT 0,
    zone_id INT NULL,
    user_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_allowed (is_allowed),
    INDEX idx_coordinates (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;