-- =================================================================
-- Appmative MySQL 8.0 Production DDL Schema
-- Strict Relational Engine: InnoDB
-- Charset: utf8mb4 / Collation: utf8mb4_unicode_ci
-- =================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users & Roles (RBAC)
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin', 'tech_lead', 'content_editor', 'sales_manager') NOT NULL DEFAULT 'content_editor',
  `status` ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  `remember_token` VARCHAR(100) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Services Table
DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `title` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `tagline` VARCHAR(255) NOT NULL,
  `summary` TEXT NOT NULL,
  `description` LONGTEXT NOT NULL,
  `deliverables` JSON NOT NULL,
  `technologies` JSON NOT NULL,
  `benefits` JSON NOT NULL,
  `icon` VARCHAR(100) NOT NULL DEFAULT 'Code2',
  `price_range` VARCHAR(100) NOT NULL,
  `timeline` VARCHAR(100) NOT NULL,
  `featured` BOOLEAN NOT NULL DEFAULT TRUE,
  `status` ENUM('published', 'draft') NOT NULL DEFAULT 'published',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_services_slug` (`slug`),
  INDEX `idx_services_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Industries Table
DROP TABLE IF EXISTS `industries`;
CREATE TABLE `industries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `icon_name` VARCHAR(100) NOT NULL,
  `tagline` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `key_solutions` JSON NOT NULL,
  `featured_stat_value` VARCHAR(100) NOT NULL,
  `featured_stat_label` VARCHAR(255) NOT NULL,
  `badge` VARCHAR(100) NULL,
  `compliance_standards` JSON NOT NULL,
  `status` ENUM('published', 'draft') NOT NULL DEFAULT 'published',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_industries_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Case Studies Table
DROP TABLE IF EXISTS `case_studies`;
CREATE TABLE `case_studies` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `client` VARCHAR(255) NOT NULL,
  `tagline` VARCHAR(255) NOT NULL,
  `industry` VARCHAR(100) NOT NULL,
  `services` JSON NOT NULL,
  `summary` TEXT NOT NULL,
  `challenge` LONGTEXT NOT NULL,
  `solution` LONGTEXT NOT NULL,
  `impact_metrics` JSON NOT NULL,
  `technologies` JSON NOT NULL,
  `logo_text` VARCHAR(50) NOT NULL,
  `color_gradient` VARCHAR(255) NOT NULL,
  `testimonial_quote` TEXT NULL,
  `testimonial_author` VARCHAR(255) NULL,
  `testimonial_role` VARCHAR(255) NULL,
  `duration` VARCHAR(100) NOT NULL,
  `featured` BOOLEAN NOT NULL DEFAULT TRUE,
  `status` ENUM('published', 'draft') NOT NULL DEFAULT 'published',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_case_studies_slug` (`slug`),
  INDEX `idx_case_studies_industry` (`industry`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Blog Posts Table (with Rich SEO metadata)
DROP TABLE IF EXISTS `blog_posts`;
CREATE TABLE `blog_posts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `title` VARCHAR(255) NOT NULL,
  `excerpt` TEXT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `author_name` VARCHAR(255) NOT NULL,
  `author_role` VARCHAR(255) NOT NULL,
  `author_avatar` VARCHAR(500) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `tags` JSON NOT NULL,
  `read_time` VARCHAR(50) NOT NULL,
  `published_at` DATE NOT NULL,
  `featured_image` VARCHAR(500) NOT NULL,
  `meta_title` VARCHAR(255) NOT NULL,
  `meta_description` TEXT NOT NULL,
  `canonical_url` VARCHAR(500) NOT NULL,
  `status` ENUM('published', 'draft', 'scheduled') NOT NULL DEFAULT 'published',
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_blog_slug` (`slug`),
  INDEX `idx_blog_category` (`category`),
  INDEX `idx_blog_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Inquiries Table
DROP TABLE IF EXISTS `inquiries`;
CREATE TABLE `inquiries` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(100) NULL,
  `company` VARCHAR(255) NULL,
  `service` VARCHAR(255) NOT NULL,
  `budget` VARCHAR(100) NOT NULL,
  `timeline` VARCHAR(100) NOT NULL,
  `message` LONGTEXT NOT NULL,
  `status` ENUM('new', 'in_review', 'contacted', 'proposal_sent', 'closed') NOT NULL DEFAULT 'new',
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_inquiries_status` (`status`),
  INDEX `idx_inquiries_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Audit Logs Table
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_email` VARCHAR(255) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `resource` VARCHAR(255) NOT NULL,
  `details` TEXT NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_audit_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;