-- ============================================================================
-- CRM Database Schema for Ahsankm - UNIVERSAL VERSION
-- Works with any database name
-- ============================================================================
-- 
-- INSTRUCTIONS:
-- 1. Create a database on your hosting (any name, e.g., crm_ahsankm)
-- 2. Import this file using phpMyAdmin
-- 3. No need to edit - works with any database!
-- ============================================================================

-- ============================================================================
-- Table: users
-- Stores user accounts with hashed passwords
-- ============================================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('master', 'admin', 'collector') NOT NULL DEFAULT 'collector',
  `full_name` VARCHAR(100) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_username` (`username`),
  INDEX `idx_role` (`role`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: user_permissions
-- Granular permissions for each user
-- ============================================================================
CREATE TABLE IF NOT EXISTS `user_permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `can_view_leads` TINYINT(1) NOT NULL DEFAULT 1,
  `can_edit_leads` TINYINT(1) NOT NULL DEFAULT 0,
  `can_delete_leads` TINYINT(1) NOT NULL DEFAULT 0,
  `can_export_leads` TINYINT(1) NOT NULL DEFAULT 1,
  `can_manage_users` TINYINT(1) NOT NULL DEFAULT 0,
  `can_manage_sources` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user` (`user_id`),
  CONSTRAINT `fk_permissions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: lead_sources
-- Data sources (Google Sheets, API, Manual, etc.)
-- ============================================================================
CREATE TABLE IF NOT EXISTS `lead_sources` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `type` ENUM('spreadsheet', 'api', 'manual', 'form') NOT NULL DEFAULT 'manual',
  `url` TEXT DEFAULT NULL,
  `config` JSON DEFAULT NULL COMMENT 'Store sheet GID, API keys, etc.',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: leads
-- Customer/lead information
-- ============================================================================
CREATE TABLE IF NOT EXISTS `leads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `external_id` VARCHAR(100) DEFAULT NULL COMMENT 'ID from Google Sheets or external source',
  `name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `address` TEXT DEFAULT NULL,
  `donation_type` VARCHAR(100) DEFAULT NULL,
  `source_id` INT UNSIGNED DEFAULT NULL,
  `status` ENUM('new', 'contacted', 'confirmed', 'cancelled') NOT NULL DEFAULT 'new',
  `notes` TEXT DEFAULT NULL,
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `updated_by` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_phone` (`phone`),
  INDEX `idx_status` (`status`),
  INDEX `idx_source` (`source_id`),
  INDEX `idx_assigned` (`assigned_to`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_external_id` (`external_id`),
  CONSTRAINT `fk_leads_source` FOREIGN KEY (`source_id`) REFERENCES `lead_sources` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_leads_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: sessions
-- User session management for security
-- ============================================================================
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(255) NOT NULL UNIQUE,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_token` (`token`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_expires` (`expires_at`),
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: audit_log
-- Track all important actions for security and compliance
-- ============================================================================
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `action` VARCHAR(50) NOT NULL,
  `resource_type` VARCHAR(50) NOT NULL COMMENT 'leads, users, sources, etc.',
  `resource_id` INT UNSIGNED DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_resource` (`resource_type`, `resource_id`),
  INDEX `idx_created` (`created_at`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: api_keys
-- For Google Apps Script and external integrations
-- ============================================================================
CREATE TABLE IF NOT EXISTS `api_keys` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_name` VARCHAR(100) NOT NULL,
  `api_key` VARCHAR(255) NOT NULL UNIQUE,
  `api_secret` VARCHAR(255) DEFAULT NULL,
  `permissions` JSON DEFAULT NULL COMMENT 'What this key can access',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_key` (`api_key`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: settings
-- Application-wide settings
-- ============================================================================
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `description` TEXT DEFAULT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_setting` (`setting_key`),
  CONSTRAINT `fk_settings_user` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INSERT DEFAULT DATA
-- ============================================================================

-- Insert default lead sources
INSERT IGNORE INTO `lead_sources` (`id`, `name`, `type`, `url`, `config`, `is_active`) VALUES
(1, 'جدول البيانات الرئيسي', 'spreadsheet', 'https://docs.google.com/spreadsheets/d/1MPZOYU9PLyXhsYJfsDjE9Q8qMRlAHgKdNAHz84Ms_SQ', '{"gid": 0, "sheet_name": "Sheet1"}', 1),
(2, 'إعلانات فيسبوك', 'api', 'https://facebook.com/ads', NULL, 1),
(3, 'إعلانات سناب شات', 'spreadsheet', 'https://docs.google.com/spreadsheets/d/1MPZOYU9PLyXhsYJfsDjE9Q8qMRlAHgKdNAHz84Ms_SQ', '{"gid": 913068036, "sheet_name": "snap form data"}', 1),
(4, 'إعلانات تيك توك', 'api', 'https://tiktok.com/ads', NULL, 1);

-- Insert default users
-- Default password for ALL users: ChangeMe123!
-- Bcrypt hash: $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi
-- ⚠️ CHANGE ALL PASSWORDS IMMEDIATELY AFTER FIRST LOGIN!
INSERT IGNORE INTO `users` (`id`, `username`, `password_hash`, `role`, `full_name`, `is_active`) VALUES
(1, 'master', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'master', 'المدير الرئيسي', 1),
(2, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'المسؤول', 1),
(3, 'user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'collector', 'المحصل', 1);

-- Insert permissions for default users
INSERT IGNORE INTO `user_permissions` (`user_id`, `can_view_leads`, `can_edit_leads`, `can_delete_leads`, `can_export_leads`, `can_manage_users`, `can_manage_sources`) VALUES
(1, 1, 1, 1, 1, 1, 1), -- master: full access
(2, 1, 1, 1, 1, 1, 0), -- admin: all except sources
(3, 1, 1, 0, 1, 0, 0); -- user/collector: view, edit and export

-- Insert default settings
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('app_name', 'منصة وقف إحسانكم الخيري - CRM', 'Application name'),
('sync_interval', '30', 'Auto-sync interval in seconds'),
('max_login_attempts', '5', 'Maximum login attempts before lockout'),
('session_timeout', '86400', 'Session timeout in seconds (24 hours)'),
('api_rate_limit', '100', 'API requests per minute per IP');

-- Generate a default API key for Google Apps Script
INSERT IGNORE INTO `api_keys` (`key_name`, `api_key`, `permissions`, `is_active`) VALUES
('Google Apps Script', 'ahsankm_crm_2025_apps_script_key', '{"can_insert_leads": true, "can_update_leads": true}', 1);

-- ============================================================================
-- Additional Indexes for Performance
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_leads_status_created ON leads(status, created_at);
CREATE INDEX IF NOT EXISTS idx_leads_phone_name ON leads(phone, name);
CREATE INDEX IF NOT EXISTS idx_audit_user_action ON audit_log(user_id, action);
CREATE INDEX IF NOT EXISTS idx_sessions_user_expires ON sessions(user_id, expires_at);

-- ============================================================================
-- DEFAULT LOGIN CREDENTIALS
-- ============================================================================
-- Username: master  | Password: ChangeMe123!
-- Username: admin   | Password: ChangeMe123!
-- Username: user    | Password: ChangeMe123!
--
-- ⚠️ CHANGE ALL PASSWORDS IMMEDIATELY AFTER FIRST LOGIN!
-- ============================================================================