-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 06, 2025 at 09:02 PM
-- Server version: 10.1.29-MariaDB
-- PHP Version: 7.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `kawnsoftdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `blog_categories`
--

CREATE TABLE `blog_categories` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `blog_categories`
--

INSERT INTO `blog_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `color`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'Technology', 'التكنولوجيا', 'Latest technology trends and insights', 'أحدث اتجاهات التكنولوجيا والرؤى', '#3498db', 1, 1, '2025-10-06 14:44:08'),
(2, 'Development', 'التطوير', 'Programming and development tutorials', 'دروس البرمجة والتطوير', '#2ecc71', 2, 1, '2025-10-06 14:44:08'),
(3, 'Business', 'الأعمال', 'Business and entrepreneurship topics', 'مواضيع الأعمال وريادة الأعمال', '#e74c3c', 3, 1, '2025-10-06 14:44:08'),
(4, 'Company News', 'أخبار الشركة', 'Updates and news from our company', 'تحديثات وأخبار من شركتنا', '#f39c12', 4, 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `blog_comments`
--

CREATE TABLE `blog_comments` (
  `id` int(11) NOT NULL,
  `post_id` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `author_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `author_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `author_website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `status` enum('approved','pending','spam') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `likes_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `blog_posts`
--

CREATE TABLE `blog_posts` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `excerpt_en` text COLLATE utf8mb4_unicode_ci,
  `excerpt_ar` text COLLATE utf8mb4_unicode_ci,
  `content_en` longtext COLLATE utf8mb4_unicode_ci,
  `content_ar` longtext COLLATE utf8mb4_unicode_ci,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gallery_images` text COLLATE utf8mb4_unicode_ci,
  `tags` text COLLATE utf8mb4_unicode_ci,
  `meta_title_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_title_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description_en` text COLLATE utf8mb4_unicode_ci,
  `meta_description_ar` text COLLATE utf8mb4_unicode_ci,
  `status` enum('published','draft','archived') COLLATE utf8mb4_unicode_ci DEFAULT 'draft',
  `read_time` int(11) DEFAULT NULL,
  `views_count` int(11) DEFAULT '0',
  `likes_count` int(11) DEFAULT '0',
  `shares_count` int(11) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_pinned` tinyint(1) DEFAULT '0',
  `allow_comments` tinyint(1) DEFAULT '1',
  `published_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `logo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `industry` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_person` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contact_submissions`
--

CREATE TABLE `contact_submissions` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `company` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subject` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `service_interest` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `budget_range` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timeline` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `is_read` tinyint(1) DEFAULT '0',
  `is_responded` tinyint(1) DEFAULT '0',
  `submitted_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `dynamic_pages`
--

CREATE TABLE `dynamic_pages` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content_en` longtext COLLATE utf8mb4_unicode_ci,
  `content_ar` longtext COLLATE utf8mb4_unicode_ci,
  `excerpt_en` text COLLATE utf8mb4_unicode_ci,
  `excerpt_ar` text COLLATE utf8mb4_unicode_ci,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `template` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'default',
  `meta_title_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_title_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description_en` text COLLATE utf8mb4_unicode_ci,
  `meta_description_ar` text COLLATE utf8mb4_unicode_ci,
  `status` enum('published','draft','private') COLLATE utf8mb4_unicode_ci DEFAULT 'draft',
  `sort_order` int(11) DEFAULT '0',
  `show_in_menu` tinyint(1) DEFAULT '1',
  `show_in_footer` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description_en` longtext COLLATE utf8mb4_unicode_ci,
  `description_ar` longtext COLLATE utf8mb4_unicode_ci,
  `short_description_en` text COLLATE utf8mb4_unicode_ci,
  `short_description_ar` text COLLATE utf8mb4_unicode_ci,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_type` enum('conference','workshop','webinar','meetup','training') COLLATE utf8mb4_unicode_ci DEFAULT 'workshop',
  `venue_en` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `venue_ar` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `venue_map_url` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_datetime` datetime DEFAULT NULL,
  `end_datetime` datetime DEFAULT NULL,
  `timezone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_attendees` int(11) DEFAULT NULL,
  `registration_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(8,2) DEFAULT '0.00',
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'YER',
  `tags` text COLLATE utf8mb4_unicode_ci,
  `status` enum('upcoming','ongoing','completed','cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'upcoming',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_online` tinyint(1) DEFAULT '0',
  `views_count` int(11) DEFAULT '0',
  `registrations_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `homepage_sections`
--

CREATE TABLE `homepage_sections` (
  `id` int(11) NOT NULL,
  `section_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `section_type` enum('hero','services','portfolio','stats','testimonials','news','products','custom') COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_en` text COLLATE utf8mb4_unicode_ci,
  `title_ar` text COLLATE utf8mb4_unicode_ci,
  `content_en` longtext COLLATE utf8mb4_unicode_ci,
  `content_ar` longtext COLLATE utf8mb4_unicode_ci,
  `background_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `background_color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text_color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `settings` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `homepage_sections`
--

INSERT INTO `homepage_sections` (`id`, `section_key`, `section_type`, `title_en`, `title_ar`, `content_en`, `content_ar`, `background_image`, `background_color`, `text_color`, `sort_order`, `is_active`, `settings`, `created_at`, `updated_at`) VALUES
(1, 'hero', 'hero', 'Professional Software Solutions', 'حلول برمجية احترافية', 'We deliver cutting-edge software solutions tailored to your business needs.', 'نقدم حلول برمجية متطورة مصممة خصيصاً لاحتياجات عملك.', NULL, NULL, NULL, 1, 1, NULL, '2025-10-06 14:44:08', '2025-10-06 14:44:08'),
(2, 'services', 'services', 'Our Services', 'خدماتنا', 'Comprehensive software development services', 'خدمات تطوير برمجيات شاملة', NULL, NULL, NULL, 2, 1, NULL, '2025-10-06 14:44:08', '2025-10-06 14:44:08'),
(3, 'portfolio', 'portfolio', 'Our Work', 'أعمالنا', 'Explore our successful projects and case studies', 'استكشف مشاريعنا الناجحة ودراسات الحالة', NULL, NULL, NULL, 3, 1, NULL, '2025-10-06 14:44:08', '2025-10-06 14:44:08'),
(4, 'testimonials', 'testimonials', 'What Our Clients Say', 'ماذا يقول عملاؤنا', 'Hear from our satisfied clients', 'استمع إلى آراء عملائنا الراضين', NULL, NULL, NULL, 4, 1, NULL, '2025-10-06 14:44:08', '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `job_applications`
--

CREATE TABLE `job_applications` (
  `id` int(11) NOT NULL,
  `job_title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applicant_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `applicant_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `applicant_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cover_letter` text COLLATE utf8mb4_unicode_ci,
  `resume_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','reviewed','interview','rejected','hired') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `applied_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
  `id` int(11) NOT NULL,
  `code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `native_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `direction` enum('ltr','rtl') COLLATE utf8mb4_unicode_ci DEFAULT 'ltr',
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `flag_icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `languages`
--

INSERT INTO `languages` (`id`, `code`, `name`, `native_name`, `direction`, `is_default`, `is_active`, `flag_icon`, `created_at`) VALUES
(1, 'en', 'English', 'English', 'ltr', 1, 1, 'flags/us.svg', '2025-10-06 15:11:20'),
(2, 'ar', 'Arabic', 'العربية', 'rtl', 0, 1, 'flags/sa.svg', '2025-10-06 15:11:20');

-- --------------------------------------------------------

--
-- Table structure for table `media`
--

CREATE TABLE `media` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `mime_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_extension` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alt_text_en` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alt_text_ar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption_en` text COLLATE utf8mb4_unicode_ci,
  `caption_ar` text COLLATE utf8mb4_unicode_ci,
  `dimensions` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT '1',
  `upload_type` enum('image','video','document','audio') COLLATE utf8mb4_unicode_ci DEFAULT 'image',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `news`
--

CREATE TABLE `news` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `excerpt_en` text COLLATE utf8mb4_unicode_ci,
  `excerpt_ar` text COLLATE utf8mb4_unicode_ci,
  `content_en` longtext COLLATE utf8mb4_unicode_ci,
  `content_ar` longtext COLLATE utf8mb4_unicode_ci,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gallery_images` text COLLATE utf8mb4_unicode_ci,
  `tags` text COLLATE utf8mb4_unicode_ci,
  `meta_title_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_title_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description_en` text COLLATE utf8mb4_unicode_ci,
  `meta_description_ar` text COLLATE utf8mb4_unicode_ci,
  `status` enum('published','draft','archived') COLLATE utf8mb4_unicode_ci DEFAULT 'draft',
  `read_time` int(11) DEFAULT NULL,
  `views_count` int(11) DEFAULT '0',
  `shares_count` int(11) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_pinned` tinyint(1) DEFAULT '0',
  `published_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `newsletter_subscriptions`
--

CREATE TABLE `newsletter_subscriptions` (
  `id` int(11) NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `language` enum('en','ar') COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `is_active` tinyint(1) DEFAULT '1',
  `subscribed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `unsubscribed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `news_categories`
--

CREATE TABLE `news_categories` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `news_categories`
--

INSERT INTO `news_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `color`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'Company Updates', 'تحديثات الشركة', 'Latest company news and updates', 'أحدث أخبار وتحديثات الشركة', '#3498db', 1, 1, '2025-10-06 14:44:08'),
(2, 'Product Releases', 'إصدارات المنتجات', 'New product launches and updates', 'إطلاق وتحديثات المنتجات الجديدة', '#2ecc71', 2, 1, '2025-10-06 14:44:08'),
(3, 'Industry News', 'أخبار الصناعة', 'Technology and industry developments', 'تطورات التكنولوجيا والصناعة', '#e74c3c', 3, 1, '2025-10-06 14:44:08'),
(4, 'Events', 'الأحداث', 'Company events and announcements', 'أحداث وإعلانات الشركة', '#f39c12', 4, 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL,
  `permission_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `permission_key`, `description`, `category`, `created_at`) VALUES
(1, 'content.manage', 'Manage all content', 'content', '2025-10-06 14:44:08'),
(2, 'content.create', 'Create new content', 'content', '2025-10-06 14:44:08'),
(3, 'users.manage', 'Manage users and roles', 'users', '2025-10-06 14:44:08'),
(4, 'settings.manage', 'Manage site settings', 'settings', '2025-10-06 14:44:08'),
(5, 'media.manage', 'Manage media library', 'media', '2025-10-06 14:44:08'),
(6, 'media.upload', 'Upload media files', 'media', '2025-10-06 14:44:08'),
(7, 'blog.manage', 'Manage blog posts', 'blog', '2025-10-06 14:44:08'),
(8, 'blog.create', 'Create blog posts', 'blog', '2025-10-06 14:44:08'),
(9, 'news.manage', 'Manage news articles', 'news', '2025-10-06 14:44:08'),
(10, 'analytics.view', 'View analytics and reports', 'analytics', '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `portfolio_categories`
--

CREATE TABLE `portfolio_categories` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `portfolio_categories`
--

INSERT INTO `portfolio_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `icon`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'Web Development', 'تطوير المواقع', 'Custom websites and web applications', 'مواقع وتطبيقات ويب مخصصة', 'fas fa-code', 1, 1, '2025-10-06 14:44:08'),
(2, 'Mobile Apps', 'تطبيقات الهواتف', 'iOS and Android mobile applications', 'تطبيقات الهواتف المحمولة', 'fas fa-mobile-alt', 2, 1, '2025-10-06 14:44:08'),
(3, 'E-commerce', 'التجارة الإلكترونية', 'Online stores and e-commerce solutions', 'متاجر إلكترونية وحلول التجارة الإلكترونية', 'fas fa-shopping-cart', 3, 1, '2025-10-06 14:44:08'),
(4, 'Enterprise Solutions', 'الحلول المؤسسية', 'Business and enterprise software', 'برمجيات الأعمال والمؤسسات', 'fas fa-building', 4, 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `portfolio_items`
--

CREATE TABLE `portfolio_items` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `challenge_en` longtext COLLATE utf8mb4_unicode_ci,
  `challenge_ar` longtext COLLATE utf8mb4_unicode_ci,
  `solution_en` longtext COLLATE utf8mb4_unicode_ci,
  `solution_ar` longtext COLLATE utf8mb4_unicode_ci,
  `results_en` longtext COLLATE utf8mb4_unicode_ci,
  `results_ar` longtext COLLATE utf8mb4_unicode_ci,
  `technologies` text COLLATE utf8mb4_unicode_ci,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gallery_images` text COLLATE utf8mb4_unicode_ci,
  `project_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `github_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `video_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `budget` decimal(12,2) DEFAULT NULL,
  `team_size` int(11) DEFAULT NULL,
  `client_testimonial_en` text COLLATE utf8mb4_unicode_ci,
  `client_testimonial_ar` text COLLATE utf8mb4_unicode_ci,
  `client_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_position` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_company` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `views_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tagline_en` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tagline_ar` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description_en` longtext COLLATE utf8mb4_unicode_ci,
  `description_ar` longtext COLLATE utf8mb4_unicode_ci,
  `features` text COLLATE utf8mb4_unicode_ci,
  `specifications` text COLLATE utf8mb4_unicode_ci,
  `pricing` text COLLATE utf8mb4_unicode_ci,
  `demo_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `documentation_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `download_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `screenshots` text COLLATE utf8mb4_unicode_ci,
  `technologies` text COLLATE utf8mb4_unicode_ci,
  `version` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `release_date` date DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `views_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_categories`
--

CREATE TABLE `product_categories` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_categories`
--

INSERT INTO `product_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `icon`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'Web Applications', 'تطبيقات الويب', 'Web-based software solutions', 'حلول برمجية قائمة على الويب', 'fas fa-globe', 1, 1, '2025-10-06 14:44:08'),
(2, 'Desktop Software', 'برامج سطح المكتب', 'Windows and cross-platform applications', 'تطبيقات ويندوز ومتعددة المنصات', 'fas fa-desktop', 2, 1, '2025-10-06 14:44:08'),
(3, 'Mobile Apps', 'تطبيقات الهواتف', 'iOS and Android applications', 'تطبيقات آيفون وأندرويد', 'fas fa-mobile-alt', 3, 1, '2025-10-06 14:44:08'),
(4, 'Cloud Solutions', 'حلول السحابة', 'Cloud-based software and services', 'برمجيات وخدمات سحابية', 'fas fa-cloud', 4, 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `permissions` text COLLATE utf8mb4_unicode_ci,
  `is_system_role` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `description`, `permissions`, `is_system_role`, `created_at`) VALUES
(1, 'super_admin', 'Full system access with all privileges', '[\"*\"]', 1, '2025-10-06 14:44:08'),
(2, 'admin', 'Administrator with most privileges', '[\"content.manage\", \"users.manage\", \"settings.manage\", \"media.manage\"]', 1, '2025-10-06 14:44:08'),
(3, 'editor', 'Can manage content but not users', '[\"content.manage\", \"media.manage\", \"blog.manage\", \"news.manage\"]', 1, '2025-10-06 14:44:08'),
(4, 'author', 'Can write and manage own content', '[\"content.create\", \"media.upload\", \"blog.create\"]', 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `title_en` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title_ar` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slug_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `short_description_en` text COLLATE utf8mb4_unicode_ci,
  `short_description_ar` text COLLATE utf8mb4_unicode_ci,
  `full_description_en` longtext COLLATE utf8mb4_unicode_ci,
  `full_description_ar` longtext COLLATE utf8mb4_unicode_ci,
  `icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `featured_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gallery_images` text COLLATE utf8mb4_unicode_ci,
  `technologies` text COLLATE utf8mb4_unicode_ci,
  `features` text COLLATE utf8mb4_unicode_ci,
  `pricing_models` text COLLATE utf8mb4_unicode_ci,
  `faqs` text COLLATE utf8mb4_unicode_ci,
  `meta_title_en` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_title_ar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description_en` text COLLATE utf8mb4_unicode_ci,
  `meta_description_ar` text COLLATE utf8mb4_unicode_ci,
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `views_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `service_categories`
--

CREATE TABLE `service_categories` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_categories`
--

INSERT INTO `service_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `icon`, `color`, `sort_order`, `is_active`, `created_at`) VALUES
(1, 'Web Development', 'تطوير المواقع', 'Custom web applications and websites', 'تطبيقات ومواقع ويب مخصصة', 'fas fa-code', '#3498db', 1, 1, '2025-10-06 14:44:08'),
(2, 'Mobile Development', 'تطوير التطبيقات', 'iOS and Android mobile applications', 'تطبيقات الهواتف المحمولة', 'fas fa-mobile-alt', '#e74c3c', 2, 1, '2025-10-06 14:44:08'),
(3, 'Software Solutions', 'الحلول البرمجية', 'Custom software and business solutions', 'حلول برمجية وتجارية مخصصة', 'fas fa-laptop-code', '#2ecc71', 3, 1, '2025-10-06 14:44:08'),
(4, 'Consulting', 'الاستشارات', 'Technical consulting and IT strategy', 'الاستشارات التقنية واستراتيجية تكنولوجيا المعلومات', 'fas fa-lightbulb', '#f39c12', 4, 1, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `site_settings`
--

CREATE TABLE `site_settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value_en` text COLLATE utf8mb4_unicode_ci,
  `value_ar` text COLLATE utf8mb4_unicode_ci,
  `setting_type` enum('text','textarea','boolean','number','json','image','color') COLLATE utf8mb4_unicode_ci DEFAULT 'text',
  `category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description_en` text COLLATE utf8mb4_unicode_ci,
  `description_ar` text COLLATE utf8mb4_unicode_ci,
  `sort_order` int(11) DEFAULT '0',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `site_settings`
--

INSERT INTO `site_settings` (`id`, `setting_key`, `value_en`, `value_ar`, `setting_type`, `category`, `description_en`, `description_ar`, `sort_order`, `updated_at`) VALUES
(1, 'site_name', 'KawnSoft', 'كاون سوفت', 'text', 'general', 'Website name', 'اسم الموقع', 0, '2025-10-06 14:44:08'),
(2, 'site_description', 'Professional Software Solutions', 'حلول برمجية احترافية', 'textarea', 'general', 'Website description', 'وصف الموقع', 0, '2025-10-06 14:44:08'),
(3, 'contact_email', 'info@kawnsoft.com', 'info@kawnsoft.com', 'text', 'contact', 'Contact email address', 'عنوان البريد الإلكتروني للتواصل', 0, '2025-10-06 14:44:08'),
(4, 'contact_phone', '+967 123 456 789', '+967 123 456 789', 'text', 'contact', 'Contact phone number', 'رقم الهاتف للتواصل', 0, '2025-10-06 14:44:08'),
(5, 'default_language', 'en', 'en', 'text', 'language', 'Default website language', 'اللغة الافتراضية للموقع', 0, '2025-10-06 14:44:08'),
(6, 'timezone', 'Asia/Aden', 'Asia/Aden', 'text', 'general', 'Website timezone', 'المنطقة الزمنية للموقع', 0, '2025-10-06 14:44:08'),
(7, 'currency', 'YER', 'YER', 'text', 'general', 'Default currency', 'العملة الافتراضية', 0, '2025-10-06 14:44:08'),
(8, 'social_facebook', '', '', 'text', 'social', 'Facebook page URL', 'رابط صفحة الفيسبوك', 0, '2025-10-06 14:44:08'),
(9, 'social_twitter', '', '', 'text', 'social', 'Twitter profile URL', 'رابط حساب تويتر', 0, '2025-10-06 14:44:08'),
(10, 'social_linkedin', '', '', 'text', 'social', 'LinkedIn company page URL', 'رابط صفحة لينكد إن', 0, '2025-10-06 14:44:08'),
(11, 'social_github', '', '', 'text', 'social', 'GitHub organization URL', 'رابط منظمة جيت هاب', 0, '2025-10-06 14:44:08');

-- --------------------------------------------------------

--
-- Table structure for table `team_members`
--

CREATE TABLE `team_members` (
  `id` int(11) NOT NULL,
  `name_en` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position_en` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position_ar` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bio_en` text COLLATE utf8mb4_unicode_ci,
  `bio_ar` text COLLATE utf8mb4_unicode_ci,
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linkedin_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `github_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitter_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `skills` text COLLATE utf8mb4_unicode_ci,
  `department` enum('development','design','management','marketing','support') COLLATE utf8mb4_unicode_ci DEFAULT 'development',
  `join_date` date DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_lead` tinyint(1) DEFAULT '0',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `testimonials`
--

CREATE TABLE `testimonials` (
  `id` int(11) NOT NULL,
  `client_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_position` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_company` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content_en` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `content_ar` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `translations`
--

CREATE TABLE `translations` (
  `id` int(11) NOT NULL,
  `translation_key` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `translation_en` text COLLATE utf8mb4_unicode_ci,
  `translation_ar` text COLLATE utf8mb4_unicode_ci,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `language` enum('en','ar') COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `timezone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'Asia/Aden',
  `is_active` tinyint(1) DEFAULT '1',
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT '0',
  `last_activity` datetime DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT '0',
  `email_verification_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password_reset_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password_reset_expires` datetime DEFAULT NULL,
  `two_factor_secret` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `blog_categories`
--
ALTER TABLE `blog_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_blog_categories_active` (`is_active`),
  ADD KEY `idx_blog_categories_order` (`sort_order`);

--
-- Indexes for table `blog_comments`
--
ALTER TABLE `blog_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_blog_comments_post` (`post_id`),
  ADD KEY `idx_blog_comments_parent` (`parent_id`),
  ADD KEY `idx_blog_comments_status` (`status`),
  ADD KEY `idx_blog_comments_created` (`created_at`);

--
-- Indexes for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_blog_posts_category` (`category_id`),
  ADD KEY `idx_blog_posts_author` (`author_id`),
  ADD KEY `idx_blog_posts_slug_en` (`slug_en`),
  ADD KEY `idx_blog_posts_slug_ar` (`slug_ar`),
  ADD KEY `idx_blog_posts_status` (`status`),
  ADD KEY `idx_blog_posts_featured` (`is_featured`),
  ADD KEY `idx_blog_posts_published` (`published_at`),
  ADD KEY `idx_blog_posts_views` (`views_count`);

--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_clients_name` (`name`),
  ADD KEY `idx_clients_industry` (`industry`),
  ADD KEY `idx_clients_active` (`is_active`);

--
-- Indexes for table `contact_submissions`
--
ALTER TABLE `contact_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_contact_email` (`email`),
  ADD KEY `idx_contact_read` (`is_read`),
  ADD KEY `idx_contact_submitted` (`submitted_at`);

--
-- Indexes for table `dynamic_pages`
--
ALTER TABLE `dynamic_pages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug_en` (`slug_en`),
  ADD UNIQUE KEY `slug_ar` (`slug_ar`),
  ADD KEY `idx_dynamic_pages_slug_en` (`slug_en`),
  ADD KEY `idx_dynamic_pages_slug_ar` (`slug_ar`),
  ADD KEY `idx_dynamic_pages_status` (`status`),
  ADD KEY `idx_dynamic_pages_parent` (`parent_id`),
  ADD KEY `idx_dynamic_pages_menu` (`show_in_menu`);

--
-- Indexes for table `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_events_type` (`event_type`),
  ADD KEY `idx_events_status` (`status`),
  ADD KEY `idx_events_featured` (`is_featured`),
  ADD KEY `idx_events_start` (`start_datetime`),
  ADD KEY `idx_events_views` (`views_count`);

--
-- Indexes for table `homepage_sections`
--
ALTER TABLE `homepage_sections`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `section_key` (`section_key`),
  ADD KEY `idx_homepage_sections_key` (`section_key`),
  ADD KEY `idx_homepage_sections_type` (`section_type`),
  ADD KEY `idx_homepage_sections_order` (`sort_order`),
  ADD KEY `idx_homepage_sections_active` (`is_active`);

--
-- Indexes for table `job_applications`
--
ALTER TABLE `job_applications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_job_applications_email` (`applicant_email`),
  ADD KEY `idx_job_applications_status` (`status`),
  ADD KEY `idx_job_applications_applied` (`applied_at`);

--
-- Indexes for table `languages`
--
ALTER TABLE `languages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`),
  ADD KEY `idx_languages_code` (`code`),
  ADD KEY `idx_languages_active` (`is_active`);

--
-- Indexes for table `media`
--
ALTER TABLE `media`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_media_user` (`user_id`),
  ADD KEY `idx_media_type` (`upload_type`),
  ADD KEY `idx_media_public` (`is_public`),
  ADD KEY `idx_media_created` (`created_at`);

--
-- Indexes for table `news`
--
ALTER TABLE `news`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_news_category` (`category_id`),
  ADD KEY `idx_news_author` (`author_id`),
  ADD KEY `idx_news_slug_en` (`slug_en`),
  ADD KEY `idx_news_slug_ar` (`slug_ar`),
  ADD KEY `idx_news_status` (`status`),
  ADD KEY `idx_news_featured` (`is_featured`),
  ADD KEY `idx_news_published` (`published_at`),
  ADD KEY `idx_news_views` (`views_count`);

--
-- Indexes for table `newsletter_subscriptions`
--
ALTER TABLE `newsletter_subscriptions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_newsletter_email` (`email`),
  ADD KEY `idx_newsletter_active` (`is_active`),
  ADD KEY `idx_newsletter_language` (`language`);

--
-- Indexes for table `news_categories`
--
ALTER TABLE `news_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_news_categories_active` (`is_active`),
  ADD KEY `idx_news_categories_order` (`sort_order`);

--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `permission_key` (`permission_key`),
  ADD KEY `idx_permissions_key` (`permission_key`),
  ADD KEY `idx_permissions_category` (`category`);

--
-- Indexes for table `portfolio_categories`
--
ALTER TABLE `portfolio_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_portfolio_categories_active` (`is_active`),
  ADD KEY `idx_portfolio_categories_order` (`sort_order`);

--
-- Indexes for table `portfolio_items`
--
ALTER TABLE `portfolio_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_portfolio_category` (`category_id`),
  ADD KEY `idx_portfolio_client` (`client_id`),
  ADD KEY `idx_portfolio_slug_en` (`slug_en`),
  ADD KEY `idx_portfolio_slug_ar` (`slug_ar`),
  ADD KEY `idx_portfolio_featured` (`is_featured`),
  ADD KEY `idx_portfolio_active` (`is_active`),
  ADD KEY `idx_portfolio_views` (`views_count`),
  ADD KEY `idx_portfolio_dates` (`start_date`,`end_date`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_products_category` (`category_id`),
  ADD KEY `idx_products_slug_en` (`slug_en`),
  ADD KEY `idx_products_slug_ar` (`slug_ar`),
  ADD KEY `idx_products_featured` (`is_featured`),
  ADD KEY `idx_products_active` (`is_active`),
  ADD KEY `idx_products_views` (`views_count`),
  ADD KEY `idx_products_release` (`release_date`);

--
-- Indexes for table `product_categories`
--
ALTER TABLE `product_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_categories_active` (`is_active`),
  ADD KEY `idx_product_categories_order` (`sort_order`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`),
  ADD KEY `idx_roles_name` (`name`),
  ADD KEY `idx_roles_system` (`is_system_role`);

--
-- Indexes for table `services`
--
ALTER TABLE `services`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_services_category` (`category_id`),
  ADD KEY `idx_services_slug_en` (`slug_en`),
  ADD KEY `idx_services_slug_ar` (`slug_ar`),
  ADD KEY `idx_services_featured` (`is_featured`),
  ADD KEY `idx_services_active` (`is_active`),
  ADD KEY `idx_services_views` (`views_count`);

--
-- Indexes for table `service_categories`
--
ALTER TABLE `service_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_service_categories_active` (`is_active`),
  ADD KEY `idx_service_categories_order` (`sort_order`);

--
-- Indexes for table `site_settings`
--
ALTER TABLE `site_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`),
  ADD KEY `idx_site_settings_key` (`setting_key`),
  ADD KEY `idx_site_settings_category` (`category`);

--
-- Indexes for table `team_members`
--
ALTER TABLE `team_members`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_team_department` (`department`),
  ADD KEY `idx_team_active` (`is_active`),
  ADD KEY `idx_team_lead` (`is_lead`),
  ADD KEY `idx_team_order` (`sort_order`);

--
-- Indexes for table `testimonials`
--
ALTER TABLE `testimonials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_testimonials_featured` (`is_featured`),
  ADD KEY `idx_testimonials_active` (`is_active`),
  ADD KEY `idx_testimonials_project` (`project_id`),
  ADD KEY `idx_testimonials_order` (`sort_order`);

--
-- Indexes for table `translations`
--
ALTER TABLE `translations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `translation_key` (`translation_key`),
  ADD KEY `idx_translations_key` (`translation_key`),
  ADD KEY `idx_translations_active` (`is_active`),
  ADD KEY `idx_translations_category` (`category`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_users_email` (`email`),
  ADD KEY `idx_users_username` (`username`),
  ADD KEY `idx_users_active` (`is_active`),
  ADD KEY `idx_users_role` (`role_id`),
  ADD KEY `idx_users_language` (`language`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `blog_categories`
--
ALTER TABLE `blog_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `blog_comments`
--
ALTER TABLE `blog_comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `blog_posts`
--
ALTER TABLE `blog_posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contact_submissions`
--
ALTER TABLE `contact_submissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dynamic_pages`
--
ALTER TABLE `dynamic_pages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `events`
--
ALTER TABLE `events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `homepage_sections`
--
ALTER TABLE `homepage_sections`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `job_applications`
--
ALTER TABLE `job_applications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `languages`
--
ALTER TABLE `languages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `media`
--
ALTER TABLE `media`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `news`
--
ALTER TABLE `news`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `newsletter_subscriptions`
--
ALTER TABLE `newsletter_subscriptions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `news_categories`
--
ALTER TABLE `news_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `portfolio_categories`
--
ALTER TABLE `portfolio_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `portfolio_items`
--
ALTER TABLE `portfolio_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `product_categories`
--
ALTER TABLE `product_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `services`
--
ALTER TABLE `services`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `service_categories`
--
ALTER TABLE `service_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `site_settings`
--
ALTER TABLE `site_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `team_members`
--
ALTER TABLE `team_members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `testimonials`
--
ALTER TABLE `testimonials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `translations`
--
ALTER TABLE `translations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `blog_comments`
--
ALTER TABLE `blog_comments`
  ADD CONSTRAINT `blog_comments_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `blog_comments_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `blog_comments` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD CONSTRAINT `blog_posts_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `blog_posts_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `dynamic_pages`
--
ALTER TABLE `dynamic_pages`
  ADD CONSTRAINT `dynamic_pages_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `dynamic_pages` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `media`
--
ALTER TABLE `media`
  ADD CONSTRAINT `media_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `news`
--
ALTER TABLE `news`
  ADD CONSTRAINT `news_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `news_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `news_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `portfolio_items`
--
ALTER TABLE `portfolio_items`
  ADD CONSTRAINT `portfolio_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `portfolio_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `portfolio_items_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `services`
--
ALTER TABLE `services`
  ADD CONSTRAINT `services_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `testimonials`
--
ALTER TABLE `testimonials`
  ADD CONSTRAINT `testimonials_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `portfolio_items` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
