-- Leadmatix Database Schema
-- MySQL 8.0+ compatible
-- Run this script in phpMyAdmin or MySQL command line

-- Create database (uncomment if needed)
-- CREATE DATABASE IF NOT EXISTS leadmatix CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE leadmatix;

-- Users table (Owner and Client authentication)
CREATE TABLE IF NOT EXISTS `users` (
  `id` varchar(25) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `role` enum('OWNER','CLIENT') NOT NULL DEFAULT 'CLIENT',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_key` (`email`),
  KEY `idx_users_role` (`role`),
  KEY `idx_users_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clients table (Tenant/client accounts)
CREATE TABLE IF NOT EXISTS `clients` (
  `id` varchar(25) NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` enum('ACTIVE','FROZEN','DELETED') NOT NULL DEFAULT 'ACTIVE',
  `api_key` varchar(64) NOT NULL,
  `api_secret` varchar(255) NOT NULL,
  `notif_emails` text,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `clients_api_key_key` (`api_key`),
  KEY `idx_clients_status` (`status`),
  KEY `idx_clients_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Popups table (Popup configurations per client)
CREATE TABLE IF NOT EXISTS `popups` (
  `id` varchar(25) NOT NULL,
  `client_id` varchar(25) NOT NULL,
  `name` varchar(255) NOT NULL,
  `position` enum('BOTTOM_LEFT','BOTTOM_CENTER','BOTTOM_RIGHT','TOP_LEFT','TOP_CENTER','TOP_RIGHT') NOT NULL DEFAULT 'BOTTOM_CENTER',
  `delay_ms` int NOT NULL DEFAULT 3000,
  `animate` tinyint(1) NOT NULL DEFAULT 1,
  `color_bg` varchar(7) NOT NULL DEFAULT '#ffffff',
  `color_text` varchar(7) NOT NULL DEFAULT '#333333',
  `color_btn_bg` varchar(7) NOT NULL DEFAULT '#007cba',
  `color_btn_text` varchar(7) NOT NULL DEFAULT '#ffffff',
  `cta_heading` varchar(255) NOT NULL DEFAULT 'Would You Like a Quotation?',
  `cta_subheading` varchar(255),
  `logo_path` varchar(500),
  `show_once_mode` tinyint(1) NOT NULL DEFAULT 1,
  `spam_protection_level` int NOT NULL DEFAULT 1,
  `z_index` int NOT NULL DEFAULT 999999,
  `breakpoint_visibility` json,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_popups_client_id` (`client_id`),
  KEY `idx_popups_created_at` (`created_at`),
  CONSTRAINT `popups_client_id_fkey` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Popup fields table (Dynamic form fields per popup)
CREATE TABLE IF NOT EXISTS `popup_fields` (
  `id` varchar(25) NOT NULL,
  `popup_id` varchar(25) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `label` varchar(255) NOT NULL,
  `type` enum('TEXT','EMAIL','TEL','TEXTAREA','DROPDOWN','RADIO','CHECKBOX','HIDDEN') NOT NULL,
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `placeholder` varchar(255),
  `help_text` varchar(500),
  `options_json` text,
  `sort_order` int NOT NULL DEFAULT 0,
  `validations_json` json,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_popup_fields_popup_id` (`popup_id`),
  KEY `idx_popup_fields_sort_order` (`sort_order`),
  CONSTRAINT `popup_fields_popup_id_fkey` FOREIGN KEY (`popup_id`) REFERENCES `popups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Leads table (Lead submissions with client isolation)
CREATE TABLE IF NOT EXISTS `leads` (
  `id` varchar(25) NOT NULL,
  `client_id` varchar(25) NOT NULL,
  `popup_id` varchar(25) NOT NULL,
  `status` enum('NEW','PENDING','COMPLETE') NOT NULL DEFAULT 'NEW',
  `name` varchar(255) NOT NULL,
  `email` varchar(255),
  `phone` varchar(50),
  `payload_json` longtext NOT NULL,
  `ip_hash` varchar(64),
  `ua_hash` varchar(64),
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `deleted_at` datetime(3),
  PRIMARY KEY (`id`),
  KEY `idx_leads_client_id_created_at` (`client_id`, `created_at`),
  KEY `idx_leads_status` (`status`),
  KEY `idx_leads_created_at` (`created_at`),
  KEY `idx_leads_deleted_at` (`deleted_at`),
  CONSTRAINT `leads_client_id_fkey` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leads_popup_id_fkey` FOREIGN KEY (`popup_id`) REFERENCES `popups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Impressions table (Unique visitor tracking)
CREATE TABLE IF NOT EXISTS `impressions` (
  `id` varchar(25) NOT NULL,
  `client_id` varchar(25) NOT NULL,
  `popup_id` varchar(25) NOT NULL,
  `visitor_token` varchar(255) NOT NULL,
  `ip_hash` varchar(64),
  `ua_hash` varchar(64),
  `occurred_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `meta_json` json,
  PRIMARY KEY (`id`),
  UNIQUE KEY `impressions_visitor_token_key` (`visitor_token`),
  KEY `idx_impressions_client_id_occurred_at` (`client_id`, `occurred_at`),
  KEY `idx_impressions_occurred_at` (`occurred_at`),
  CONSTRAINT `impressions_client_id_fkey` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `impressions_popup_id_fkey` FOREIGN KEY (`popup_id`) REFERENCES `popups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit logs table (Security and action logging)
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` varchar(25) NOT NULL,
  `actor_user_id` varchar(25),
  `action` varchar(100) NOT NULL,
  `target_type` varchar(50),
  `target_id` varchar(25),
  `detail_json` json,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `idx_audit_logs_actor_user_id` (`actor_user_id`),
  KEY `idx_audit_logs_target_type_target_id` (`target_type`, `target_id`),
  KEY `idx_audit_logs_created_at` (`created_at`),
  CONSTRAINT `audit_logs_actor_user_id_fkey` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `audit_logs_target_id_fkey` FOREIGN KEY (`target_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create views for analytics
CREATE OR REPLACE VIEW `daily_stats` AS
SELECT 
  DATE(created_at) as date,
  client_id,
  COUNT(*) as leads_count
FROM leads 
WHERE deleted_at IS NULL
GROUP BY DATE(created_at), client_id;

CREATE OR REPLACE VIEW `daily_impressions` AS
SELECT 
  DATE(occurred_at) as date,
  client_id,
  COUNT(*) as impressions_count
FROM impressions 
GROUP BY DATE(occurred_at), client_id;

-- Create indexes for better performance
CREATE INDEX `idx_leads_email` ON `leads` (`email`);
CREATE INDEX `idx_leads_phone` ON `leads` (`phone`);
CREATE INDEX `idx_leads_name` ON `leads` (`name`);

-- Insert sample data (optional - uncomment to add test data)
/*
-- Insert owner user
INSERT INTO `users` (`id`, `email`, `password_hash`, `name`, `role`, `is_active`) VALUES
('owner_001', 'admin@leadmatix.com', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4J/8QzKz2K', 'Leadmatix Admin', 'OWNER', 1);

-- Insert sample client
INSERT INTO `clients` (`id`, `name`, `status`, `api_key`, `api_secret`, `notif_emails`) VALUES
('client_001', 'Sample Client', 'ACTIVE', 'sample_api_key_123', '$2a$12$sample_hashed_secret', '["client@example.com"]');
*/

-- Show table creation summary
SELECT 'Database schema created successfully!' as message;
SELECT COUNT(*) as table_count FROM information_schema.tables WHERE table_schema = DATABASE();
