-- ============================================================
-- CV Cloud Point Indonesia - Database Schema + Seed Data
-- MySQL Import File
-- ============================================================


-- -----------------------------------------------------------
-- Table: services
-- -----------------------------------------------------------
CREATE TABLE `services` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `name_id` VARCHAR(191) NOT NULL,
  `name_en` VARCHAR(191) NOT NULL,
  `description_id` TEXT NOT NULL,
  `description_en` TEXT NOT NULL,
  `benefits_id` TEXT DEFAULT NULL,
  `benefits_en` TEXT DEFAULT NULL,
  `icon` VARCHAR(191) DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `order_index` INT NOT NULL DEFAULT 0,
  `is_active` BOOLEAN NOT NULL DEFAULT true,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `services_slug_id_key` (`slug_id`),
  UNIQUE KEY `services_slug_en_key` (`slug_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: portofolios
-- -----------------------------------------------------------
CREATE TABLE `portofolios` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `title_id` VARCHAR(191) NOT NULL,
  `title_en` VARCHAR(191) NOT NULL,
  `client_name` VARCHAR(191) DEFAULT NULL,
  `description_id` TEXT DEFAULT NULL,
  `description_en` TEXT DEFAULT NULL,
  `images` TEXT DEFAULT NULL,
  `category` VARCHAR(191) DEFAULT NULL,
  `year` INT DEFAULT NULL,
  `link` VARCHAR(500) DEFAULT NULL,
  `is_featured` BOOLEAN NOT NULL DEFAULT false,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `portofolios_slug_id_key` (`slug_id`),
  UNIQUE KEY `portofolios_slug_en_key` (`slug_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: testimonials
-- -----------------------------------------------------------
CREATE TABLE `testimonials` (
  `id` VARCHAR(191) NOT NULL,
  `client_name` VARCHAR(191) NOT NULL,
  `company` VARCHAR(191) DEFAULT NULL,
  `photo` VARCHAR(500) DEFAULT NULL,
  `content_id` TEXT NOT NULL,
  `content_en` TEXT NOT NULL,
  `rating` INT NOT NULL DEFAULT 5,
  `is_active` BOOLEAN NOT NULL DEFAULT true,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: clients
-- -----------------------------------------------------------
CREATE TABLE `clients` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `logo` VARCHAR(500) NOT NULL,
  `website_url` VARCHAR(500) DEFAULT NULL,
  `is_active` BOOLEAN NOT NULL DEFAULT true,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: pages
-- -----------------------------------------------------------
CREATE TABLE `pages` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `title_id` VARCHAR(191) NOT NULL,
  `title_en` VARCHAR(191) NOT NULL,
  `content_id` LONGTEXT DEFAULT NULL,
  `content_en` LONGTEXT DEFAULT NULL,
  `meta_title_id` VARCHAR(191) DEFAULT NULL,
  `meta_title_en` VARCHAR(191) DEFAULT NULL,
  `meta_description_id` TEXT DEFAULT NULL,
  `meta_description_en` TEXT DEFAULT NULL,
  `is_published` BOOLEAN NOT NULL DEFAULT true,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `pages_slug_id_key` (`slug_id`),
  UNIQUE KEY `pages_slug_en_key` (`slug_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: blog_categories
-- -----------------------------------------------------------
CREATE TABLE `blog_categories` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `name_id` VARCHAR(191) NOT NULL,
  `name_en` VARCHAR(191) NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_categories_slug_id_key` (`slug_id`),
  UNIQUE KEY `blog_categories_slug_en_key` (`slug_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: blog_tags
-- -----------------------------------------------------------
CREATE TABLE `blog_tags` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `name_id` VARCHAR(191) NOT NULL,
  `name_en` VARCHAR(191) NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_tags_slug_id_key` (`slug_id`),
  UNIQUE KEY `blog_tags_slug_en_key` (`slug_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: blog_posts
-- -----------------------------------------------------------
CREATE TABLE `blog_posts` (
  `id` VARCHAR(191) NOT NULL,
  `slug_id` VARCHAR(191) NOT NULL,
  `slug_en` VARCHAR(191) NOT NULL,
  `title_id` VARCHAR(191) NOT NULL,
  `title_en` VARCHAR(191) NOT NULL,
  `excerpt_id` TEXT DEFAULT NULL,
  `excerpt_en` TEXT DEFAULT NULL,
  `content_id` LONGTEXT DEFAULT NULL,
  `content_en` LONGTEXT DEFAULT NULL,
  `featured_image` VARCHAR(500) DEFAULT NULL,
  `author_id` VARCHAR(191) DEFAULT NULL,
  `category_id` VARCHAR(191) DEFAULT NULL,
  `tags` TEXT DEFAULT NULL,
  `is_published` BOOLEAN NOT NULL DEFAULT false,
  `published_at` DATETIME(3) DEFAULT NULL,
  `meta_title_id` VARCHAR(191) DEFAULT NULL,
  `meta_title_en` VARCHAR(191) DEFAULT NULL,
  `meta_description_id` TEXT DEFAULT NULL,
  `meta_description_en` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `blog_posts_slug_id_key` (`slug_id`),
  UNIQUE KEY `blog_posts_slug_en_key` (`slug_en`),
  KEY `blog_posts_author_id_fkey` (`author_id`),
  KEY `blog_posts_category_id_fkey` (`category_id`),
  CONSTRAINT `blog_posts_author_id_fkey` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `blog_posts_category_id_fkey` FOREIGN KEY (`category_id`) REFERENCES `blog_categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: blog_post_tags
-- -----------------------------------------------------------
CREATE TABLE `blog_post_tags` (
  `post_id` VARCHAR(191) NOT NULL,
  `tag_id` VARCHAR(191) NOT NULL,
  PRIMARY KEY (`post_id`, `tag_id`),
  KEY `blog_post_tags_tag_id_fkey` (`tag_id`),
  CONSTRAINT `blog_post_tags_post_id_fkey` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `blog_post_tags_tag_id_fkey` FOREIGN KEY (`tag_id`) REFERENCES `blog_tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: leads
-- -----------------------------------------------------------
CREATE TABLE `leads` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `phone` VARCHAR(191) DEFAULT NULL,
  `company` VARCHAR(191) DEFAULT NULL,
  `service_interest` VARCHAR(191) DEFAULT NULL,
  `message` TEXT DEFAULT NULL,
  `source` VARCHAR(191) NOT NULL DEFAULT 'contact_form',
  `status` VARCHAR(191) NOT NULL DEFAULT 'new',
  `notes` TEXT DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Table: media
-- -----------------------------------------------------------
CREATE TABLE `media` (
  `id` VARCHAR(191) NOT NULL,
  `filename` VARCHAR(500) NOT NULL,
  `original_name` VARCHAR(500) NOT NULL,
  `mime_type` VARCHAR(191) NOT NULL,
  `size` INT NOT NULL,
  `path` VARCHAR(500) NOT NULL,
  `alt_text` VARCHAR(191) DEFAULT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Services
-- -----------------------------------------------------------
INSERT INTO `services` (`id`, `slug_id`, `slug_en`, `name_id`, `name_en`, `description_id`, `description_en`, `benefits_id`, `benefits_en`, `icon`, `image`, `order_index`, `is_active`, `created_at`, `updated_at`)
VALUES
('svc001', 'google-workspace', 'google-workspace', 'Google Workspace', 'Google Workspace', 'Jual beli lisensi Google Workspace resmi untuk bisnis Anda. Dapatkan akses ke Gmail, Google Drive, Google Meet, dan layanan Google lainnya.', 'Official Google Workspace license reseller for your business. Get access to Gmail, Google Drive, Google Meet, and other Google services.', '["Akun email profesional","Penyimpanan cloud","Video meeting","Kolaborasi real-time"]', '["Professional email","Cloud storage","Video meetings","Real-time collaboration"]', NULL, NULL, 1, 1, NOW(3), NOW(3)),
('svc002', 'pembuatan-website', 'website-development', 'Pembuatan Website', 'Website Development', 'Jasa pembuatan website profesional untuk company profile, e-commerce, dan kebutuhan bisnis lainnya.', 'Professional website development services for company profile, e-commerce, and other business needs.', '["Desain modern","Responsif","SEO friendly","Loading cepat"]', '["Modern design","Responsive","SEO friendly","Fast loading"]', NULL, NULL, 2, 1, NOW(3), NOW(3)),
('svc003', 'pembuatan-aplikasi', 'app-development', 'Pembuatan Aplikasi', 'App Development', 'Pengembangan aplikasi mobile dan web yang disesuaikan dengan kebutuhan bisnis Anda.', 'Mobile and web application development tailored to your business needs.', '["Android & iOS","Web app","UI/UX profesional","Scalable"]', '["Android & iOS","Web app","Professional UI/UX","Scalable"]', NULL, NULL, 3, 1, NOW(3), NOW(3)),
('svc004', 'konsultasi-it', 'it-consulting', 'Konsultasi IT', 'IT Consulting', 'Layanan konsultasi IT untuk membantu bisnis Anda dalam transformasi digital.', 'IT consulting services to help your business with digital transformation.', '["Analisis kebutuhan","Transformasi digital","Infrastruktur","Roadmap teknologi"]', '["Needs analysis","Digital transformation","Infrastructure","Technology roadmap"]', NULL, NULL, 4, 1, NOW(3), NOW(3));

-- -----------------------------------------------------------
-- Testimonials
-- -----------------------------------------------------------
INSERT INTO `testimonials` (`id`, `client_name`, `company`, `photo`, `content_id`, `content_en`, `rating`, `is_active`, `created_at`)
VALUES
('tst001', 'Budi Santoso', 'PT Maju Jaya', NULL, 'Layanan Google Workspace dari Cloud Point sangat membantu produktivitas tim kami. Proses migrasi berjalan lancar.', 'Google Workspace service from Cloud Point greatly helped our team productivity. The migration process went smoothly.', 5, 1, NOW(3)),
('tst002', 'Siti Rahayu', 'UD Berkah', NULL, 'Website yang dibuat oleh Cloud Point sesuai dengan ekspektasi kami. Desainnya profesional dan loadingnya cepat.', 'The website created by Cloud Point met our expectations. The design is professional and loads fast.', 5, 1, NOW(3)),
('tst003', 'Ahmad Fauzi', 'CV Sukses Mandiri', NULL, 'Konsultasi IT yang diberikan sangat membantu kami dalam merencanakan transformasi digital perusahaan.', 'The IT consulting provided greatly helped us in planning our company''s digital transformation.', 5, 1, NOW(3));

-- -----------------------------------------------------------
-- Blog Categories
-- -----------------------------------------------------------
INSERT INTO `blog_categories` (`id`, `slug_id`, `slug_en`, `name_id`, `name_en`, `created_at`)
VALUES
('cat001', 'google-workspace', 'google-workspace', 'Google Workspace', 'Google Workspace', NOW(3)),
('cat002', 'tips', 'tips', 'Tips', 'Tips', NOW(3)),
('cat003', 'konsultasi-it', 'it-consulting', 'Konsultasi IT', 'IT Consulting', NOW(3)),
('cat004', 'berita', 'news', 'Berita', 'News', NOW(3));

