-- Geographic Registration Control System (Updated for Polygon Zones)
-- Tables for zone management and registration tracking

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

-- Table: allowed_zones
-- Stores geographic zones as polygons where registration is permitted
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 defining the polygon',
    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),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: registration_attempts
-- Logs all registration attempts with geolocation data and user details
CREATE TABLE registration_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mobile VARCHAR(15) NOT NULL,
    name VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    pin_code VARCHAR(10) NULL,
    user_type VARCHAR(20) NULL COMMENT 'seller or buyer',
    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 COMMENT 'ID of the zone if allowed',
    user_id INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_location (state, city),
    INDEX idx_created (created_at),
    INDEX idx_allowed (is_allowed),
    INDEX idx_ip (ip_address),
    INDEX idx_coordinates (latitude, longitude),
    INDEX idx_user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;