-- ============================================================
-- Database Schema for Anchor Backend
-- Database: shubhamheroweby_anchor
-- ============================================================

-- 1. Clients Table (Dedicated Customers Table)
CREATE TABLE IF NOT EXISTS `clients` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `phone_number` VARCHAR(30) DEFAULT NULL,
  `company_name` VARCHAR(150) DEFAULT NULL,
  `street_address` VARCHAR(255) DEFAULT NULL,
  `street_address_2` VARCHAR(255) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `postcode` VARCHAR(20) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret` VARCHAR(255) DEFAULT NULL,
  `profile_picture` VARCHAR(255) DEFAULT NULL,
  `is_email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `is_phone_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_client_email` (`email`),
  INDEX `idx_client_phone` (`phone_number`),
  INDEX `idx_client_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Staff Table (Dedicated Admin & Supporters Table)
CREATE TABLE IF NOT EXISTS `staff` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `phone_number` VARCHAR(30) DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'supporter') NOT NULL DEFAULT 'supporter',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret` VARCHAR(255) DEFAULT NULL,
  `profile_picture` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_staff_email` (`email`),
  INDEX `idx_staff_role` (`role`),
  INDEX `idx_staff_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Refresh Tokens Table (Works for both Clients and Staff)
CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `user_type` ENUM('client', 'staff') NOT NULL DEFAULT 'client',
  `token` TEXT NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_user_lookup` (`user_id`, `user_type`),
  INDEX `idx_token` (`token`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Verification Codes / OTP Table (For Client Email & Phone Verification)
CREATE TABLE IF NOT EXISTS `verification_codes` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `client_id` BIGINT(20) UNSIGNED NOT NULL,
  `type` ENUM('email', 'phone') NOT NULL,
  `target` VARCHAR(255) NOT NULL,
  `code` VARCHAR(20) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_used` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_client_type` (`client_id`, `type`),
  INDEX `idx_code` (`code`),
  CONSTRAINT `fk_verification_codes_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Departments Table
CREATE TABLE IF NOT EXISTS `departments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `deptid` INT UNSIGNED NOT NULL UNIQUE,
  `name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial Seed Data for Departments
INSERT INTO `departments` (`deptid`, `name`, `description`) VALUES
  (1, 'Technical Support', 'Help with technical issues and server configurations'),
  (4, 'Billing Department', 'Invoices, payments, upgrades and account billing'),
  (5, 'Sales & Pre Sales Department', 'Pre-sales inquiries, product details, custom plans'),
  (6, 'Abuse Department', 'Reports of abuse, DMCA, and network policy violations')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `description`=VALUES(`description`);

-- 6. Tickets Table (Client Support Tickets)
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_number` VARCHAR(30) NOT NULL UNIQUE,
  `client_id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` INT UNSIGNED NOT NULL,
  `assigned_staff_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `priority` ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL DEFAULT 'Medium',
  `status` ENUM('Open', 'In Progress', 'Answered', 'Customer-Reply', 'Closed') NOT NULL DEFAULT 'Open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_client_id` (`client_id`),
  INDEX `idx_department_id` (`department_id`),
  INDEX `idx_assigned_staff_id` (`assigned_staff_id`),
  INDEX `idx_status` (`status`),
  CONSTRAINT `fk_tickets_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tickets_assigned_staff` FOREIGN KEY (`assigned_staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tickets_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Ticket Messages / Threaded Replies Table
CREATE TABLE IF NOT EXISTS `ticket_messages` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT(20) UNSIGNED NOT NULL,
  `client_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `staff_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `sender_type` ENUM('client', 'support', 'admin') NOT NULL DEFAULT 'client',
  `message` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ticket_id` (`ticket_id`),
  INDEX `idx_client_id` (`client_id`),
  INDEX `idx_staff_id` (`staff_id`),
  CONSTRAINT `fk_ticket_messages_ticket_id` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ticket_messages_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ticket_messages_staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Ticket Attachments Table (Images, Videos, Documents, PDFs)
CREATE TABLE IF NOT EXISTS `ticket_attachments` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` BIGINT(20) UNSIGNED NOT NULL,
  `message_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_type` VARCHAR(100) DEFAULT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ticket_id` (`ticket_id`),
  INDEX `idx_message_id` (`message_id`),
  CONSTRAINT `fk_ticket_attachments_ticket_id` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ticket_attachments_message_id` FOREIGN KEY (`message_id`) REFERENCES `ticket_messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Permissions Catalog Table (System Granular Permissions)
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `name` VARCHAR(150) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `category` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial Seed Data for System Permissions
INSERT INTO `permissions` (`slug`, `name`, `description`, `category`) VALUES
  ('clients.view', 'View Clients', 'View client list and client profiles', 'Clients'),
  ('clients.create', 'Create Clients', 'Create new client accounts directly', 'Clients'),
  ('clients.edit', 'Edit Clients', 'Edit existing client account details', 'Clients'),
  ('clients.delete', 'Delete Clients', 'Delete or deactivate client accounts', 'Clients'),
  ('tickets.view_all', 'View All Tickets', 'View tickets from all clients and departments', 'Tickets'),
  ('tickets.reply', 'Reply to Tickets', 'Post staff replies on customer tickets', 'Tickets'),
  ('tickets.edit_status', 'Manage Ticket Status', 'Change ticket status, priority, or department', 'Tickets'),
  ('tickets.delete', 'Delete Tickets', 'Permanently delete tickets and attachments', 'Tickets'),
  ('departments.view', 'View Departments', 'View support department listing', 'Departments'),
  ('departments.manage', 'Manage Departments', 'Create, update, and manage support departments', 'Departments'),
  ('staff.view', 'View Staff', 'View list of supporters and staff members', 'Staff'),
  ('staff.manage', 'Manage Staff', 'Create, edit supporters and assign permissions', 'Staff')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `description`=VALUES(`description`), `category`=VALUES(`category`);

-- 10. Staff Permissions Mapping Table (Dynamic Permissions for Supporters in Staff table)
CREATE TABLE IF NOT EXISTS `staff_permissions` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `staff_id` BIGINT(20) UNSIGNED NOT NULL,
  `permission_slug` VARCHAR(100) NOT NULL,
  `granted_by` BIGINT(20) UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_staff_permission` (`staff_id`, `permission_slug`),
  INDEX `idx_staff_id` (`staff_id`),
  INDEX `idx_permission_slug` (`permission_slug`),
  CONSTRAINT `fk_staff_permissions_staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;