-- Create users table for authentication and profile management
-- This table stores user account information and permissions

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `supabase_id` VARCHAR(255) UNIQUE NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `is_admin` BOOLEAN DEFAULT FALSE,
    `is_active` BOOLEAN DEFAULT TRUE,
    `avatar_url` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_login` TIMESTAMP NULL,
    
    -- Indexes for performance
    INDEX `idx_email` (`email`),
    INDEX `idx_supabase_id` (`supabase_id`),
    INDEX `idx_is_admin` (`is_admin`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create default admin user (update with your details)
INSERT INTO `users` (`email`, `first_name`, `last_name`, `is_admin`, `is_active`) VALUES
('admin@tile.map.one', 'Admin', 'User', TRUE, TRUE)
ON DUPLICATE KEY UPDATE 
    `is_admin` = TRUE,
    `is_active` = TRUE;
