-- =====================================================
-- Auriga Portal Database Schema
-- CodeIgniter 3.1.2
-- =====================================================

CREATE DATABASE IF NOT EXISTS `marketing_portal` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `marketing_portal`;

-- =====================================================
-- Users Table
-- =====================================================
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','user') NOT NULL DEFAULT 'user',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `gender` enum('male','female','other') DEFAULT NULL,
  `city` varchar(100) 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 `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Campaigns Table
-- =====================================================
CREATE TABLE `campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('SMS','WhatsApp','WhatsApp Image','Status Messaging','Newsletter','Custom') NOT NULL,
  `fail_percentage` decimal(5,2) NOT NULL DEFAULT 1.00,
  `status` enum('active','paused','stopped','completed') NOT NULL DEFAULT 'active',
  `created_by` int(11) NOT NULL,
  `total_assigned` int(11) NOT NULL DEFAULT 0,
  `total_sent` int(11) NOT NULL DEFAULT 0,
  `total_failed` int(11) NOT NULL DEFAULT 0,
  `total_remaining` int(11) 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`),
  KEY `idx_status` (`status`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_type` (`type`),
  CONSTRAINT `fk_campaign_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Master Data Table
-- =====================================================
CREATE TABLE `master_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `call` varchar(20) DEFAULT NULL,
  `custom_field1` varchar(255) DEFAULT NULL,
  `custom_field2` varchar(255) DEFAULT NULL,
  `custom_field3` varchar(255) DEFAULT NULL,
  `is_assigned` tinyint(1) NOT NULL DEFAULT 0,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_is_assigned` (`is_assigned`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Campaign Users Assignment Table
-- =====================================================
CREATE TABLE `campaign_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_campaign_user` (`campaign_id`,`user_id`),
  KEY `idx_campaign_id` (`campaign_id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_cu_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cu_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Campaign User Data Assignment Table
-- =====================================================
CREATE TABLE `campaign_user_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `master_data_id` int(11) NOT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `is_auto_added` tinyint(1) NOT NULL DEFAULT 0,
  `message_content` text,
  `sent_at` timestamp NULL DEFAULT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaign_user` (`campaign_id`,`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_master_data` (`master_data_id`),
  KEY `idx_is_auto_added` (`is_auto_added`),
  CONSTRAINT `fk_cud_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cud_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cud_master` FOREIGN KEY (`master_data_id`) REFERENCES `master_data` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Send Logs Table
-- =====================================================
CREATE TABLE `send_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `campaign_data_id` int(11) NOT NULL,
  `mobile` varchar(20) NOT NULL,
  `message_content` text NOT NULL,
  `status` enum('sent','failed') NOT NULL,
  `error_message` text,
  `ip_address` varchar(50) DEFAULT NULL,
  `user_agent` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaign` (`campaign_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_campaign_data` (`campaign_data_id`),
  CONSTRAINT `fk_log_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Fail Auto Add History Table
-- =====================================================
CREATE TABLE `fail_auto_add` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `original_data_id` int(11) NOT NULL,
  `new_data_id` int(11) NOT NULL,
  `reason` varchar(255) DEFAULT 'Auto-added due to send failure',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaign` (`campaign_id`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `fk_fail_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_fail_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Login Logs Table
-- =====================================================
CREATE TABLE `login_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `ip_address` varchar(50) NOT NULL,
  `user_agent` text,
  `browser` varchar(100) DEFAULT NULL,
  `device` varchar(100) DEFAULT NULL,
  `status` enum('success','failed') NOT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_login_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Activity Logs / Audit Trail Table
-- =====================================================
CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `module` varchar(100) NOT NULL,
  `description` text,
  `ip_address` varchar(50) DEFAULT NULL,
  `user_agent` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_module` (`module`),
  KEY `idx_created_at` (`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;

-- =====================================================
-- Scheduled Messages
-- =====================================================
CREATE TABLE `scheduled_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `sender_id` varchar(50) DEFAULT NULL,
  `message_content` text NOT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `total_recipients` int(11) NOT NULL,
  `parts` tinyint(3) NOT NULL DEFAULT 4,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_campaign_id` (`campaign_id`),
  KEY `idx_created_by` (`created_by`),
  CONSTRAINT `fk_sched_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sched_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Scheduled Message Batches
-- =====================================================
CREATE TABLE `scheduled_message_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scheduled_message_id` int(11) NOT NULL,
  `batch_number` tinyint(3) NOT NULL,
  `batch_size` int(11) NOT NULL,
  `scheduled_for` datetime NOT NULL,
  `status` enum('pending','processing','sent') NOT NULL DEFAULT 'pending',
  `sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sched_message` (`scheduled_message_id`),
  KEY `idx_scheduled_for` (`scheduled_for`),
  CONSTRAINT `fk_sched_batch_message` FOREIGN KEY (`scheduled_message_id`) REFERENCES `scheduled_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Insert Default Admin User
-- Password: admin123
-- =====================================================
INSERT INTO `users` (`username`, `email`, `password`, `role`, `status`) 
VALUES ('admin', 'admin@marketingportal.com', '$2y$10$xG1jX/3aATmgedoOluVtwe3gw6zcQ6RHEgu7T5412.s8fgO4FCvKa', 'admin', 'active');

-- =====================================================
-- Insert Sample Test User
-- Password: user123
-- =====================================================
INSERT INTO `users` (`username`, `email`, `password`, `role`, `status`) 
VALUES ('testuser', 'user@marketingportal.com', '$2y$10$2/2vscBbvDnIVhCIlQSU3eZ8sU/l8JkOB7XwwmMEl.N1VNHXvbrz6', 'user', 'active');

-- =====================================================
-- Insert Sample Master Data
-- =====================================================
INSERT INTO `master_data` (`mobile`, `name`, `email`, `gender`, `city`, `call`, `is_assigned`) VALUES
('9972433999', 'Neha Reddy', 'neha.reddy534@yahoo.com', 'female', 'Thane', 'Yes', 1),
('9800836252', 'Manoj Joshi', 'manoj.joshi140@gmail.com', 'male', 'Thane', 'Yes', 1),
('9761436989', 'Puja Kulkarni', 'puja.kulkarni442@yahoo.com', 'female', 'Visakhapatnam', 'No', 1),
('7760356503', 'Meera Gupta', 'meera.gupta40@outlook.com', 'female', 'Jaipur', 'Yes', 1),
('7168207402', 'Nisha Gupta', 'nisha.gupta857@gmail.com', 'female', 'Pune', 'Yes', 1),
('8553227987', 'Arjun Kumar', 'arjun.kumar732@hotmail.com', 'male', 'Bhopal', 'No', 1),
('9563011938', 'Rahul Reddy', 'rahul.reddy226@rediffmail.com', 'male', 'Mumbai', 'Yes', 1),
('8168186385', 'Nisha Chopra', 'nisha.chopra465@outlook.com', 'female', 'Kanpur', 'No', 1),
('6412542560', 'Manoj Reddy', 'manoj.reddy103@rediffmail.com', 'male', 'Vadodara', 'Yes', 1),
('6665344287', 'Ankit Joshi', 'ankit.joshi77@outlook.com', 'male', 'Delhi', 'Yes', 1);

-- =====================================================
-- SUPER ADMIN PORTAL TABLES
-- =====================================================

-- =====================================================
-- Portals Table - Store portal configurations
-- =====================================================
CREATE TABLE IF NOT EXISTS `portals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `portal_code` varchar(50) NOT NULL,
  `portal_name` varchar(255) NOT NULL,
  `portal_url` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `database_name` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  `settings` text 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 `portal_code` (`portal_code`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Super Admins Table - Super admin users
-- =====================================================
CREATE TABLE IF NOT EXISTS `super_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `last_login` datetime 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 `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Portal Activity Logs Table - Track super admin actions
-- =====================================================
CREATE TABLE IF NOT EXISTS `portal_activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `super_admin_id` int(11) NOT NULL,
  `portal_code` varchar(50) DEFAULT NULL,
  `action_type` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_super_admin_id` (`super_admin_id`),
  KEY `idx_portal_code` (`portal_code`),
  KEY `idx_action_type` (`action_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Insert Default Portals
-- =====================================================
INSERT INTO `portals` (`portal_code`, `portal_name`, `portal_url`, `description`, `database_name`, `status`) VALUES
('auriga', 'Auriga Marketing Portal', 'https://brandwave.aurigaconsulting.co.in/', 'Auriga Consulting marketing portal', 'ac3_marketing_portal', 'active'),
('trump', 'Trump Marketing Portal', 'https://360marketing.trumporganisers.in/', 'Trump Organisers marketing portal', 'ac3_marketing_portal', 'active'),
('ascensiontech', 'Ascensiontech Campaign Portal', 'https://campaign.ascensiontech.co.in/', 'Ascensiontech campaign management portal', 'ac3_marketing_portal', 'active');

-- =====================================================
-- Insert Default Super Admin
-- Username: superadmin
-- Password: superadmin123
-- =====================================================
INSERT INTO `super_admins` (`username`, `email`, `password`, `full_name`, `status`) VALUES
('superadmin', 'superadmin@marketingportal.com', '$2y$10$xG1jX/3aATmgedoOluVtwe3gw6zcQ6RHEgu7T5412.s8fgO4FCvKa', 'Super Administrator', 'active');

-- =====================================================
-- Add portal_code column to existing tables (optional)
-- Run these ALTER statements if you want to track portal ownership
-- =====================================================
-- ALTER TABLE `users` ADD COLUMN `portal_code` varchar(50) DEFAULT 'auriga' AFTER `status`;
-- ALTER TABLE `campaigns` ADD COLUMN `portal_code` varchar(50) DEFAULT 'auriga' AFTER `status`;
-- ALTER TABLE `master_data` ADD COLUMN `portal_code` varchar(50) DEFAULT 'auriga' AFTER `is_assigned`;

