-- ============================================================================
-- CRM Database Schema for Ahsankm
-- Secure Backend with PHP + MySQL
-- ============================================================================

-- Database will be created by hosting provider
-- Database name: institu3_crm
-- Connect to existing database

USE `institu3_crm`;

-- ============================================================================
-- 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.)
-- ⚠️ MUST be created BEFORE leads table (foreign key dependency)
-- ============================================================================
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
-- ⚠️ References lead_sources table (must exist first)
-- ============================================================================
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 (use INSERT IGNORE to skip if already exists)
INSERT IGNORE INTO `lead_sources` (`id`, `name`, `type`, `url`, `config`, `is_active`) VALUES
(1, 'الموقع الرئيسي', 'manual', NULL, NULL, 1),
(2, 'نموذج الويب', 'form', NULL, NULL, 1),
(3, 'سناب شات', 'spreadsheet', NULL, '{"gid": 913068036, "sheet_name": "snap form data"}', 1),
(4, 'تيك توك', 'spreadsheet', NULL, '{"gid": null, "sheet_name": "tiktok form data"}', 1);

-- Insert default users (use INSERT IGNORE to skip if already exists)
-- Password: Default password for all users is "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 (use INSERT IGNORE to skip if already exists)
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, 0, 0, 1, 0, 0); -- user/collector: view and export only

-- Insert default settings (use INSERT IGNORE to skip if already exists)
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 (use INSERT IGNORE to skip if already exists)
-- Key: ahsankm_crm_2025_apps_script_key
-- ⚠️ This should be changed in production!
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);

-- ============================================================================
-- VIEWS for easier querying
-- ============================================================================

-- View: Full user information with permissions
CREATE OR REPLACE VIEW `v_users_full` AS
SELECT 
  u.id,
  u.username,
  u.role,
  u.full_name,
  u.email,
  u.is_active,
  u.created_at,
  u.last_login,
  p.can_view_leads,
  p.can_edit_leads,
  p.can_delete_leads,
  p.can_export_leads,
  p.can_manage_users,
  p.can_manage_sources
FROM users u
LEFT JOIN user_permissions p ON u.id = p.user_id;

-- View: Leads with source information
CREATE OR REPLACE VIEW `v_leads_full` AS
SELECT 
  l.id,
  l.external_id,
  l.name,
  l.phone,
  l.address,
  l.donation_type,
  l.status,
  l.notes,
  l.created_at,
  l.updated_at,
  s.name AS source_name,
  s.type AS source_type,
  a.username AS assigned_to_username,
  c.username AS created_by_username,
  u.username AS updated_by_username
FROM leads l
LEFT JOIN lead_sources s ON l.source_id = s.id
LEFT JOIN users a ON l.assigned_to = a.id
LEFT JOIN users c ON l.created_by = c.id
LEFT JOIN users u ON l.updated_by = u.id;

-- ============================================================================
-- STORED PROCEDURES
-- ============================================================================

-- Procedure: Clean expired sessions
DROP PROCEDURE IF EXISTS `sp_clean_expired_sessions`;
DELIMITER //
CREATE PROCEDURE `sp_clean_expired_sessions`()
BEGIN
  DELETE FROM sessions WHERE expires_at < NOW();
END //
DELIMITER ;

-- Procedure: Get user dashboard statistics
DROP PROCEDURE IF EXISTS `sp_get_dashboard_stats`;
DELIMITER //
CREATE PROCEDURE `sp_get_dashboard_stats`(IN p_user_id INT UNSIGNED)
BEGIN
  SELECT
    (SELECT COUNT(*) FROM leads) AS total_leads,
    (SELECT COUNT(*) FROM leads WHERE status = 'new') AS new_leads,
    (SELECT COUNT(*) FROM leads WHERE status = 'contacted') AS contacted_leads,
    (SELECT COUNT(*) FROM leads WHERE status = 'confirmed') AS confirmed_leads,
    (SELECT COUNT(*) FROM leads WHERE status = 'cancelled') AS cancelled_leads,
    (SELECT COUNT(*) FROM leads WHERE DATE(created_at) = CURDATE()) AS today_leads,
    (SELECT COUNT(*) FROM users WHERE is_active = 1) AS active_users;
END //
DELIMITER ;

-- Procedure: Log audit action
DROP PROCEDURE IF EXISTS `sp_log_audit`;
DELIMITER //
CREATE PROCEDURE `sp_log_audit`(
  IN p_user_id INT UNSIGNED,
  IN p_action VARCHAR(50),
  IN p_resource_type VARCHAR(50),
  IN p_resource_id INT UNSIGNED,
  IN p_old_values JSON,
  IN p_new_values JSON,
  IN p_ip_address VARCHAR(45),
  IN p_user_agent TEXT
)
BEGIN
  INSERT INTO audit_log (user_id, action, resource_type, resource_id, old_values, new_values, ip_address, user_agent)
  VALUES (p_user_id, p_action, p_resource_type, p_resource_id, p_old_values, p_new_values, p_ip_address, p_user_agent);
END //
DELIMITER ;

-- ============================================================================
-- EVENTS (Cleanup tasks)
-- ============================================================================
-- NOTE: Events require SUPER privilege which is not available on shared hosting
-- Instead, create a cron job to run session cleanup:
-- 0 2 * * * mysql -h 144.76.198.187 -u institu3_crm -pcrm@2025 institu3_crm -e "CALL sp_clean_expired_sessions();"

-- Uncomment below if you have SUPER privilege (VPS/Dedicated server):
-- SET GLOBAL event_scheduler = ON;
-- CREATE EVENT IF NOT EXISTS `evt_clean_sessions`
-- ON SCHEDULE EVERY 1 DAY
-- STARTS CURRENT_TIMESTAMP
-- DO CALL sp_clean_expired_sessions();

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger: Update lead timestamp on modification
DROP TRIGGER IF EXISTS `tr_leads_before_update`;
DELIMITER //
CREATE TRIGGER `tr_leads_before_update`
BEFORE UPDATE ON `leads`
FOR EACH ROW
BEGIN
  SET NEW.updated_at = CURRENT_TIMESTAMP;
END //
DELIMITER ;

-- ============================================================================
-- INDEXES for performance
-- ============================================================================

-- Additional composite indexes for common queries
CREATE INDEX idx_leads_status_created ON leads(status, created_at);
CREATE INDEX idx_leads_phone_name ON leads(phone, name);
CREATE INDEX idx_audit_user_action ON audit_log(user_id, action);
CREATE INDEX idx_sessions_user_expires ON sessions(user_id, expires_at);

-- ============================================================================
-- GRANTS (Security)
-- ============================================================================
-- NOTE: User already exists on shared hosting (institu3_crm)
-- No need to create user or grant privileges

-- ============================================================================
-- COMPLETION MESSAGE
-- ============================================================================
-- Database schema created successfully!
-- Default password for all users: ChangeMe123!
-- Change all passwords immediately after first login!

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================