-- Padjajaran Elektrik — installer MySQL 8
-- Import hanya ke database kosong dengan charset utf8mb4.
-- Tidak membuat akun atau password administrator.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE `audit_logs` (
	`id` varchar(64) NOT NULL,
	`user_id` varchar(64),
	`action` varchar(160) NOT NULL,
	`entity_type` varchar(120) NOT NULL,
	`entity_id` varchar(64),
	`before` longtext,
	`after` longtext,
	`ip_hash` varchar(128),
	`user_agent` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `audit_logs_id` PRIMARY KEY(`id`)
);

CREATE TABLE `backups` (
	`id` varchar(64) NOT NULL,
	`storage_key` varchar(500) NOT NULL,
	`filename` varchar(255) NOT NULL DEFAULT '',
	`checksum` varchar(128) NOT NULL,
	`size` bigint NOT NULL,
	`encrypted` boolean NOT NULL DEFAULT true,
	`format_version` int NOT NULL DEFAULT 1,
	`table_count` int NOT NULL DEFAULT 0,
	`record_count` int NOT NULL DEFAULT 0,
	`status` varchar(40) NOT NULL,
	`note` text,
	`created_by` varchar(64),
	`expires_at` datetime,
	`completed_at` datetime,
	`last_verified_at` datetime,
	`last_restored_at` datetime,
	`restore_count` int NOT NULL DEFAULT 0,
	`deleted_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `backups_id` PRIMARY KEY(`id`)
);

CREATE TABLE `categories` (
	`id` varchar(64) NOT NULL,
	`name` varchar(160) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`description` text NOT NULL DEFAULT (''),
	`robots_index` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `categories_id` PRIMARY KEY(`id`),
	CONSTRAINT `categories_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `faqs` (
	`id` varchar(64) NOT NULL,
	`question` text NOT NULL,
	`answer` longtext NOT NULL,
	`category` varchar(120) NOT NULL DEFAULT 'general',
	`page_id` varchar(64),
	`sort_order` int NOT NULL DEFAULT 0,
	`active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `faqs_id` PRIMARY KEY(`id`)
);

CREATE TABLE `form_submissions` (
	`id` varchar(64) NOT NULL,
	`form_key` varchar(120) NOT NULL,
	`lead_id` varchar(64),
	`payload` longtext NOT NULL,
	`spam_score` int NOT NULL DEFAULT 0,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `form_submissions_id` PRIMARY KEY(`id`)
);

CREATE TABLE `leads` (
	`id` varchar(64) NOT NULL,
	`name` varchar(160) NOT NULL,
	`whatsapp` varchar(30) NOT NULL,
	`email` varchar(254),
	`location` text NOT NULL,
	`building_type` varchar(100) NOT NULL,
	`service` varchar(180) NOT NULL,
	`quantity` int,
	`urgency` varchar(40) NOT NULL,
	`description` longtext NOT NULL,
	`photo_media_id` varchar(64),
	`requested_schedule` datetime,
	`privacy_accepted_at` datetime NOT NULL,
	`status` varchar(40) NOT NULL DEFAULT 'new',
	`assigned_to` varchar(64),
	`utm_source` varchar(160),
	`utm_medium` varchar(160),
	`utm_campaign` varchar(200),
	`utm_content` varchar(200),
	`utm_term` varchar(200),
	`landing_page` text,
	`ip_hash` varchar(128),
	`user_agent` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `leads_id` PRIMARY KEY(`id`)
);

CREATE TABLE `login_attempts` (
	`id` varchar(64) NOT NULL,
	`email_hash` varchar(128) NOT NULL,
	`ip_hash` varchar(128) NOT NULL,
	`success` boolean NOT NULL DEFAULT false,
	`attempted_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`locked_until` datetime,
	CONSTRAINT `login_attempts_id` PRIMARY KEY(`id`)
);

CREATE TABLE `media` (
	`id` varchar(64) NOT NULL,
	`storage_key` varchar(500) NOT NULL,
	`filename` varchar(255) NOT NULL,
	`mime_type` varchar(100) NOT NULL,
	`size` bigint NOT NULL,
	`width` int,
	`height` int,
	`alt_text` text,
	`caption` text,
	`uploaded_by` varchar(64),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `media_id` PRIMARY KEY(`id`),
	CONSTRAINT `media_storage_key_idx` UNIQUE(`storage_key`)
);

CREATE TABLE `menu_items` (
	`id` varchar(64) NOT NULL,
	`menu_id` varchar(64) NOT NULL,
	`parent_id` varchar(64),
	`label` varchar(180) NOT NULL,
	`url` text NOT NULL,
	`position` int NOT NULL DEFAULT 0,
	`target` varchar(20) NOT NULL DEFAULT '_self',
	`visible` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `menu_items_id` PRIMARY KEY(`id`)
);

CREATE TABLE `menus` (
	`id` varchar(64) NOT NULL,
	`name` varchar(180) NOT NULL,
	`location` varchar(80) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `menus_id` PRIMARY KEY(`id`),
	CONSTRAINT `menus_location_idx` UNIQUE(`location`)
);

CREATE TABLE `page_blocks` (
	`id` varchar(64) NOT NULL,
	`page_id` varchar(64) NOT NULL,
	`block_type` varchar(80) NOT NULL,
	`position` int NOT NULL,
	`data` longtext NOT NULL,
	`settings` longtext,
	`visible` boolean NOT NULL DEFAULT true,
	`reusable_block_id` varchar(64),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `page_blocks_id` PRIMARY KEY(`id`)
);

CREATE TABLE `page_versions` (
	`id` varchar(64) NOT NULL,
	`page_id` varchar(64) NOT NULL,
	`version` int NOT NULL,
	`snapshot` longtext NOT NULL,
	`author_id` varchar(64),
	`note` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `page_versions_id` PRIMARY KEY(`id`),
	CONSTRAINT `page_versions_unique_idx` UNIQUE(`page_id`,`version`)
);

CREATE TABLE `pages` (
	`id` varchar(64) NOT NULL,
	`title` varchar(300) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`template` varchar(50) NOT NULL DEFAULT 'default',
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`author_id` varchar(64),
	`seo_title` varchar(300),
	`meta_description` text,
	`canonical_url` text,
	`robots_index` boolean NOT NULL DEFAULT true,
	`robots_follow` boolean NOT NULL DEFAULT true,
	`published_at` datetime,
	`scheduled_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `pages_id` PRIMARY KEY(`id`),
	CONSTRAINT `pages_slug_active_idx` UNIQUE(`slug`,`deleted_at`)
);

CREATE TABLE `permissions` (
	`id` varchar(64) NOT NULL,
	`key` varchar(120) NOT NULL,
	`description` text NOT NULL DEFAULT (''),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `permissions_id` PRIMARY KEY(`id`),
	CONSTRAINT `permissions_key_idx` UNIQUE(`key`)
);

CREATE TABLE `post_categories` (
	`post_id` varchar(64) NOT NULL,
	`category_id` varchar(64) NOT NULL,
	CONSTRAINT `post_categories_unique_idx` UNIQUE(`post_id`,`category_id`)
);

CREATE TABLE `post_tags` (
	`post_id` varchar(64) NOT NULL,
	`tag_id` varchar(64) NOT NULL,
	CONSTRAINT `post_tags_unique_idx` UNIQUE(`post_id`,`tag_id`)
);

CREATE TABLE `post_versions` (
	`id` varchar(64) NOT NULL,
	`post_id` varchar(64) NOT NULL,
	`version` int NOT NULL,
	`snapshot` longtext NOT NULL,
	`author_id` varchar(64),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `post_versions_id` PRIMARY KEY(`id`),
	CONSTRAINT `post_versions_unique_idx` UNIQUE(`post_id`,`version`)
);

CREATE TABLE `posts` (
	`id` varchar(64) NOT NULL,
	`title` varchar(400) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`excerpt` text NOT NULL DEFAULT (''),
	`content` longtext NOT NULL,
	`featured_image_id` varchar(64),
	`author_id` varchar(64),
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`focus_keyword` varchar(255),
	`secondary_keywords` longtext,
	`seo_title` varchar(300),
	`meta_description` text,
	`canonical_url` text,
	`og_title` varchar(300),
	`og_description` text,
	`og_image_id` varchar(64),
	`robots_index` boolean NOT NULL DEFAULT true,
	`robots_follow` boolean NOT NULL DEFAULT true,
	`published_at` datetime,
	`scheduled_at` datetime,
	`reading_minutes` int NOT NULL DEFAULT 1,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `posts_id` PRIMARY KEY(`id`),
	CONSTRAINT `posts_slug_active_idx` UNIQUE(`slug`,`deleted_at`)
);

CREATE TABLE `price_categories` (
	`id` varchar(64) NOT NULL,
	`name` varchar(160) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`sort_order` int NOT NULL DEFAULT 0,
	`active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `price_categories_id` PRIMARY KEY(`id`),
	CONSTRAINT `price_categories_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `price_items` (
	`id` varchar(64) NOT NULL,
	`category_id` varchar(64) NOT NULL,
	`name` varchar(300) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`price` bigint,
	`promo_price` bigint,
	`unit` varchar(80) NOT NULL,
	`price_type` varchar(50) NOT NULL DEFAULT 'service_only',
	`description` text NOT NULL DEFAULT (''),
	`active` boolean NOT NULL DEFAULT true,
	`promo_active` boolean NOT NULL DEFAULT false,
	`sort_order` int NOT NULL DEFAULT 0,
	`last_verified_at` datetime,
	`source_url` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `price_items_id` PRIMARY KEY(`id`),
	CONSTRAINT `price_items_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `project_media` (
	`id` varchar(64) NOT NULL,
	`project_id` varchar(64) NOT NULL,
	`media_id` varchar(64) NOT NULL,
	`role` varchar(40) NOT NULL DEFAULT 'gallery',
	`sort_order` int NOT NULL DEFAULT 0,
	`caption` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `project_media_id` PRIMARY KEY(`id`),
	CONSTRAINT `project_media_unique_idx` UNIQUE(`project_id`,`media_id`)
);

CREATE TABLE `projects` (
	`id` varchar(64) NOT NULL,
	`title` varchar(300) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`project_type` varchar(180) NOT NULL,
	`area` varchar(180) NOT NULL,
	`scope` text NOT NULL,
	`description` longtext NOT NULL,
	`duration` varchar(120),
	`project_year` int,
	`before_media_id` varchar(64),
	`after_media_id` varchar(64),
	`seo_title` varchar(300),
	`meta_description` text,
	`robots_index` boolean NOT NULL DEFAULT true,
	`sort_order` int NOT NULL DEFAULT 0,
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`published_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `projects_id` PRIMARY KEY(`id`),
	CONSTRAINT `projects_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `promotions` (
	`id` varchar(64) NOT NULL,
	`title` varchar(300) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`description` text NOT NULL DEFAULT (''),
	`banner_media_id` varchar(64),
	`cta_label` varchar(120),
	`cta_url` text,
	`whatsapp_template` text,
	`starts_at` datetime,
	`ends_at` datetime,
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`created_by` varchar(64),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `promotions_id` PRIMARY KEY(`id`),
	CONSTRAINT `promotions_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `redirects` (
	`id` varchar(64) NOT NULL,
	`from_path` varchar(500) NOT NULL,
	`to_path` text NOT NULL,
	`status_code` int NOT NULL DEFAULT 301,
	`hits` bigint NOT NULL DEFAULT 0,
	`active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `redirects_id` PRIMARY KEY(`id`),
	CONSTRAINT `redirects_from_idx` UNIQUE(`from_path`)
);

CREATE TABLE `reusable_blocks` (
	`id` varchar(64) NOT NULL,
	`name` varchar(200) NOT NULL,
	`block_type` varchar(80) NOT NULL,
	`data` longtext NOT NULL,
	`settings` longtext,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `reusable_blocks_id` PRIMARY KEY(`id`)
);

CREATE TABLE `role_permissions` (
	`role_id` varchar(64) NOT NULL,
	`permission_id` varchar(64) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `role_permissions_unique` UNIQUE(`role_id`,`permission_id`)
);

CREATE TABLE `roles` (
	`id` varchar(64) NOT NULL,
	`name` varchar(80) NOT NULL,
	`description` text NOT NULL DEFAULT (''),
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `roles_id` PRIMARY KEY(`id`),
	CONSTRAINT `roles_name_idx` UNIQUE(`name`)
);

CREATE TABLE `seo_settings` (
	`key` varchar(120) NOT NULL,
	`value` longtext NOT NULL,
	`updated_by` varchar(64),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `seo_settings_key` PRIMARY KEY(`key`)
);

CREATE TABLE `service_areas` (
	`id` varchar(64) NOT NULL,
	`name` varchar(180) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`city` varchar(180) NOT NULL,
	`unique_intro` text NOT NULL,
	`local_details` longtext NOT NULL,
	`geo_lat` double,
	`geo_lng` double,
	`seo_title` varchar(300),
	`meta_description` text,
	`robots_index` boolean NOT NULL DEFAULT true,
	`sort_order` int NOT NULL DEFAULT 0,
	`active` boolean NOT NULL DEFAULT true,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `service_areas_id` PRIMARY KEY(`id`),
	CONSTRAINT `service_areas_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `services` (
	`id` varchar(64) NOT NULL,
	`title` varchar(255) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`excerpt` text NOT NULL,
	`content` longtext NOT NULL,
	`icon` varchar(80),
	`featured_image_id` varchar(64),
	`status` varchar(32) NOT NULL DEFAULT 'draft',
	`sort_order` int NOT NULL DEFAULT 0,
	`seo_title` varchar(300),
	`meta_description` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `services_id` PRIMARY KEY(`id`),
	CONSTRAINT `services_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `sessions` (
	`id` varchar(64) NOT NULL,
	`user_id` varchar(64) NOT NULL,
	`token_hash` varchar(128) NOT NULL,
	`ip_hash` varchar(128),
	`user_agent` text,
	`expires_at` datetime NOT NULL,
	`invalidated_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `sessions_id` PRIMARY KEY(`id`),
	CONSTRAINT `sessions_token_idx` UNIQUE(`token_hash`)
);

CREATE TABLE `site_settings` (
	`key` varchar(120) NOT NULL,
	`value` longtext NOT NULL,
	`is_public` boolean NOT NULL DEFAULT false,
	`updated_by` varchar(64),
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `site_settings_key` PRIMARY KEY(`key`)
);

CREATE TABLE `tags` (
	`id` varchar(64) NOT NULL,
	`name` varchar(160) NOT NULL,
	`slug` varchar(255) NOT NULL,
	`robots_index` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `tags_id` PRIMARY KEY(`id`),
	CONSTRAINT `tags_slug_idx` UNIQUE(`slug`)
);

CREATE TABLE `testimonials` (
	`id` varchar(64) NOT NULL,
	`name` varchar(180) NOT NULL,
	`photo_media_id` varchar(64),
	`service_type` varchar(180),
	`area` varchar(180),
	`content` text NOT NULL,
	`source` text,
	`rating` double,
	`verified` boolean NOT NULL DEFAULT false,
	`active` boolean NOT NULL DEFAULT false,
	`sort_order` int NOT NULL DEFAULT 0,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `testimonials_id` PRIMARY KEY(`id`)
);

CREATE TABLE `users` (
	`id` varchar(64) NOT NULL,
	`email` varchar(254) NOT NULL,
	`name` varchar(120) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`password_salt` varchar(120) NOT NULL,
	`role_id` varchar(64) NOT NULL,
	`mfa_secret` varchar(128),
	`mfa_enabled` boolean NOT NULL DEFAULT false,
	`status` varchar(32) NOT NULL DEFAULT 'active',
	`password_changed_at` datetime,
	`last_login_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`deleted_at` datetime,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_idx` UNIQUE(`email`)
);

CREATE TABLE `verification_tokens` (
	`id` varchar(64) NOT NULL,
	`user_id` varchar(64),
	`type` varchar(40) NOT NULL,
	`token_hash` varchar(128) NOT NULL,
	`expires_at` datetime NOT NULL,
	`used_at` datetime,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `verification_tokens_id` PRIMARY KEY(`id`),
	CONSTRAINT `verification_token_hash_idx` UNIQUE(`token_hash`)
);

CREATE INDEX `audit_logs_entity_idx` ON `audit_logs` (`entity_type`,`entity_id`);
CREATE INDEX `audit_logs_user_idx` ON `audit_logs` (`user_id`,`created_at`);
CREATE INDEX `audit_logs_created_idx` ON `audit_logs` (`created_at`);
CREATE INDEX `audit_logs_action_idx` ON `audit_logs` (`action`,`created_at`);
CREATE INDEX `backups_status_created_idx` ON `backups` (`status`,`created_at`);
CREATE INDEX `backups_expiry_idx` ON `backups` (`expires_at`);
CREATE INDEX `leads_status_idx` ON `leads` (`status`,`created_at`);
CREATE INDEX `leads_assigned_idx` ON `leads` (`assigned_to`);
CREATE INDEX `login_attempts_lookup_idx` ON `login_attempts` (`email_hash`,`ip_hash`,`attempted_at`);
CREATE INDEX `login_attempts_time_idx` ON `login_attempts` (`attempted_at`,`success`);
CREATE INDEX `menu_items_order_idx` ON `menu_items` (`menu_id`,`position`);
CREATE INDEX `page_blocks_order_idx` ON `page_blocks` (`page_id`,`position`);
CREATE INDEX `pages_status_idx` ON `pages` (`status`);
CREATE INDEX `posts_status_idx` ON `posts` (`status`);
CREATE INDEX `price_items_category_idx` ON `price_items` (`category_id`,`sort_order`);
CREATE INDEX `project_media_order_idx` ON `project_media` (`project_id`,`sort_order`);
CREATE INDEX `projects_public_idx` ON `projects` (`status`,`sort_order`);
CREATE INDEX `promotions_period_idx` ON `promotions` (`starts_at`,`ends_at`);
CREATE INDEX `service_areas_public_idx` ON `service_areas` (`active`,`sort_order`);
CREATE INDEX `sessions_user_idx` ON `sessions` (`user_id`);
CREATE INDEX `sessions_active_idx` ON `sessions` (`invalidated_at`,`expires_at`);
CREATE INDEX `testimonials_public_idx` ON `testimonials` (`active`,`verified`,`sort_order`);
CREATE INDEX `users_role_idx` ON `users` (`role_id`);

ALTER TABLE `backups` MODIFY COLUMN `filename` varchar(255) NOT NULL DEFAULT '';
ALTER TABLE `projects` MODIFY COLUMN `description` longtext NOT NULL;

INSERT IGNORE INTO roles (id,name,description) VALUES
('role_super_admin','super_admin','Akses penuh termasuk keamanan, user, harga, integrasi, audit, dan backup'),
('role_article_writer','article_writer','Membuat dan mengedit artikel milik sendiri serta mengirim untuk review'),
('role_sales_promotion','sales_promotion','Mengelola promo, landing page terkontrol, CTA kampanye, dan lead sesuai izin');

INSERT IGNORE INTO price_categories (id,name,slug,sort_order,active) VALUES ('price_service_only','Jasa saja','jasa-saja',1,1);

INSERT IGNORE INTO price_items (id,category_id,name,slug,price,unit,price_type,active,sort_order,last_verified_at,source_url) VALUES
('price_inbow','price_service_only','Pemasangan inbow dus/mangkokan saklar','pemasangan-inbow-dus-mangkokan-saklar',15000,'unit','service_only',1,1,'2026-08-22','https://pusatlasbaja.com/product/harga-borongan-instalasi-listrik-bogor/'),
('price_led_panel','price_service_only','Pemasangan lampu LED panel tanam 3–9W','pemasangan-lampu-led-panel-tanam-3-9w',25000,'unit','service_only',1,2,'2026-08-22','https://pusatlasbaja.com/product/harga-borongan-instalasi-listrik-bogor/'),
('price_saklar_engkel','price_service_only','Pemasangan saklar engkel','pemasangan-saklar-engkel',25000,'unit','service_only',1,3,'2026-08-22','https://pusatlasbaja.com/product/harga-borongan-instalasi-listrik-bogor/'),
('price_saklar_seri','price_service_only','Pemasangan saklar seri','pemasangan-saklar-seri',30000,'unit','service_only',1,4,'2026-08-22','https://pusatlasbaja.com/product/harga-borongan-instalasi-listrik-bogor/');

INSERT IGNORE INTO pages (id,title,slug,template,status,seo_title,meta_description,published_at) VALUES
('page_home','Beranda','','home','published','Jasa Instalasi Listrik Profesional di Bogor','Instalasi, panel, grounding, tambah titik, perbaikan, dan maintenance listrik untuk Bogor.',CURRENT_TIMESTAMP),
('page_about','Tentang Kami','tentang-kami','default','published','Tentang Padjajaran Elektrik','Pendekatan kerja Padjajaran Elektrik untuk layanan kelistrikan di Bogor.',CURRENT_TIMESTAMP),
('page_survey','Permintaan Survei','permintaan-survei','form','published','Permintaan Survei Listrik Bogor','Kirim kebutuhan dan jadwal survei instalasi listrik.',CURRENT_TIMESTAMP);

INSERT IGNORE INTO page_blocks (id,page_id,block_type,position,data,visible) VALUES
('block_home_hero','page_home','Hero',1,'{"headline":"Jasa Instalasi Listrik Profesional di Bogor","cta":"Jadwalkan Survei"}',1),
('block_home_benefits','page_home','Benefit cards',2,'{"source":"site_settings"}',1),
('block_home_services','page_home','Service cards',3,'{"source":"services"}',1),
('block_home_pricing','page_home','Pricing table',4,'{"source":"price_items"}',1),
('block_home_areas','page_home','Area layanan',5,'{"source":"service_areas"}',1),
('block_home_faq','page_home','FAQ accordion',6,'{"source":"faqs"}',1),
('block_home_form','page_home','Formulir',7,'{"form":"consultation"}',1),
('block_home_cta','page_home','CTA',8,'{"headline":"Butuh Teknisi Instalasi Listrik di Bogor?"}',1);

INSERT IGNORE INTO services (id,title,slug,excerpt,content,icon,status,sort_order) VALUES
('service_home','Instalasi Listrik Rumah','instalasi-listrik-rumah','Perencanaan jalur, grup, titik lampu, saklar, dan stop kontak untuk hunian.','{"sections":[]}','home','published',1),
('service_office','Instalasi Ruko & Kantor','instalasi-ruko-kantor','Distribusi daya tertata untuk ruang kerja dan bangunan usaha.','{"sections":[]}','building','published',2),
('service_panel','Instalasi Panel Listrik','panel-listrik','Pemasangan, penataan, dan evaluasi panel serta perangkat proteksi.','{"sections":[]}','panel','published',3),
('service_point','Tambah Titik Listrik','tambah-titik-listrik','Penambahan titik lampu, saklar, dan stop kontak sesuai kebutuhan ruang.','{"sections":[]}','plus','published',4),
('service_short','Perbaikan Korsleting','perbaikan-korsleting','Pemeriksaan sumber gangguan, sambungan longgar, dan kebocoran arus.','{"sections":[]}','bolt','published',5),
('service_ground','Grounding System','grounding-system','Pemasangan dan pemeriksaan sistem pembumian instalasi.','{"sections":[]}','ground','published',6),
('service_cable','Penggantian Kabel Lama','penggantian-kabel','Evaluasi dan penggantian kabel yang menua atau tidak sesuai beban.','{"sections":[]}','cable','published',7),
('service_audit','Audit & Maintenance','audit-maintenance','Pengecekan berkala, dokumentasi temuan, dan prioritas perbaikan.','{"sections":[]}','check','published',8);

INSERT IGNORE INTO faqs (id,question,answer,sort_order,active) VALUES
('faq_1','Berapa biaya instalasi listrik per titik?','Biaya bergantung pada jenis titik, jalur kabel, kondisi bangunan, material, dan jumlah pekerjaan.',1,1),
('faq_2','Apakah harga sudah termasuk material?','Belum tentu. Item berlabel Jasa saja tidak termasuk material.',2,1),
('faq_3','Apakah tersedia survei lokasi?','Jadwal dan ketentuan survei dikonfirmasi lebih dahulu.',3,1),
('faq_4','Area Bogor mana saja yang dilayani?','Kota Bogor dan sejumlah area Kabupaten Bogor sesuai ketersediaan jadwal.',4,1),
('faq_5','Apakah bisa menangani korsleting?','Bisa setelah pemeriksaan sumber gangguan dan kondisi instalasi.',5,1),
('faq_6','Apakah bisa memasang panel listrik?','Bisa untuk pemasangan baru, perapian, penambahan grup, dan evaluasi proteksi.',6,1),
('faq_7','Berapa lama proses pengerjaan?','Durasi mengikuti jumlah titik, akses, kondisi bangunan, material, dan tingkat kesulitan.',7,1),
('faq_8','Apakah ada garansi?','Garansi pekerjaan awal 3 bulan dengan cakupan tertulis pada penawaran.',8,1),
('faq_9','Apakah bisa mengerjakan rumah, ruko, dan kantor?','Ya, berikut toko, restoran, sekolah, klinik, kos, kontrakan, dan bangunan komersial.',9,1),
('faq_10','Data apa yang perlu dikirim untuk estimasi?','Lokasi, jenis bangunan, pekerjaan, jumlah titik, urgensi, dan foto kondisi atau panel.',10,1);

INSERT IGNORE INTO categories (id,name,slug,description,robots_index) VALUES ('category_education','Edukasi Listrik','edukasi-listrik','Panduan instalasi dan perawatan kelistrikan.',0);

INSERT IGNORE INTO posts (id,title,slug,excerpt,content,status,focus_keyword,seo_title,meta_description,robots_index,robots_follow) VALUES
('post_01','Berapa Biaya Instalasi Listrik per Titik di Bogor?','biaya-instalasi-listrik-per-titik-bogor','','','draft','biaya instalasi listrik per titik Bogor','Berapa Biaya Instalasi Listrik per Titik di Bogor?','Draft artikel; lengkapi setelah data dan contoh diverifikasi.',0,1),
('post_02','Tanda Instalasi Listrik Rumah Perlu Diganti','tanda-instalasi-listrik-rumah-perlu-diganti','','','draft','instalasi listrik rumah','Tanda Instalasi Listrik Rumah Perlu Diganti','Draft artikel edukasi kelistrikan rumah.',0,1),
('post_03','Penyebab MCB Sering Turun dan Cara Menanganinya','penyebab-mcb-sering-turun','','','draft','MCB sering turun','Penyebab MCB Sering Turun','Draft artikel edukasi MCB.',0,1),
('post_04','Perbedaan MCB, ELCB, dan RCBO','perbedaan-mcb-elcb-rcbo','','','draft','perbedaan MCB ELCB RCBO','Perbedaan MCB, ELCB, dan RCBO','Draft artikel perangkat proteksi.',0,1),
('post_05','Pentingnya Grounding untuk Rumah dan Tempat Usaha','pentingnya-grounding-rumah-usaha','','','draft','grounding rumah','Pentingnya Grounding untuk Rumah dan Tempat Usaha','Draft artikel grounding.',0,1),
('post_06','Cara Menentukan Jumlah Titik Lampu dan Stop Kontak','menentukan-titik-lampu-stop-kontak','','','draft','jumlah titik listrik','Cara Menentukan Titik Lampu dan Stop Kontak','Draft artikel perencanaan titik.',0,1),
('post_07','Checklist Instalasi Listrik untuk Rumah Baru','checklist-instalasi-listrik-rumah-baru','','','draft','instalasi listrik rumah baru','Checklist Instalasi Listrik Rumah Baru','Draft checklist instalasi.',0,1),
('post_08','Risiko Menggunakan Kabel Listrik yang Tidak Sesuai Beban','risiko-kabel-tidak-sesuai-beban','','','draft','kabel listrik sesuai beban','Risiko Kabel Listrik Tidak Sesuai Beban','Draft artikel keselamatan kabel.',0,1),
('post_09','Estimasi Biaya Instalasi Listrik untuk Ruko','estimasi-biaya-instalasi-listrik-ruko','','','draft','biaya instalasi listrik ruko','Estimasi Biaya Instalasi Listrik Ruko','Draft artikel estimasi ruko.',0,1),
('post_10','Kapan Panel Listrik Perlu Di-upgrade?','kapan-panel-listrik-perlu-upgrade','','','draft','upgrade panel listrik','Kapan Panel Listrik Perlu Di-upgrade?','Draft artikel panel listrik.',0,1);

INSERT INTO site_settings (`key`,`value`,`is_public`) VALUES
('business_identity','{"name":"Padjajaran Elektrik","tagline":"Solusi Kelistrikan Aman dan Terpercaya","phone":null,"whatsapp":null,"email":null,"address":null,"maps":null,"logo":null,"certifications":null,"legal_number":null,"social":null}',1),
('operating_hours','{"monday_friday":"08.30 - 17.00","saturday":"08.30 - 15.00","sunday":null}',1),
('warranty','{"duration":"3 Bulan","terms":"Ditulis pada penawaran"}',1),
('price_disclaimer','{"text":"Estimasi awal, bukan penawaran final. Harga dapat berubah berdasarkan lokasi, jumlah titik, material, jalur kabel, kondisi bangunan, tingkat kesulitan, ketinggian, dan hasil survei."}',1),
('google_integrations','{"ga4_measurement_id":null,"gtm_container_id":null,"search_console_meta":null,"bing_verification":null,"maps_embed":null,"analytics_admin_exclusion":true}',0)
ON DUPLICATE KEY UPDATE `key`=VALUES(`key`);