-- Prestige Barbershop Professional v4.35.0 fallback SQL.
-- Jalankan hanya jika upgrade_v4_35_0.php gagal. Tidak menghapus data.

-- Tambah kolom pembayaran membership secara idempotent.
SET @db=DATABASE();
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_status'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_status ENUM('unpaid','pending','paid','rejected') NOT NULL DEFAULT 'paid' AFTER payment_method")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='paid_amount'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0 AFTER payment_status")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='cash_received'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN cash_received DECIMAL(15,2) NOT NULL DEFAULT 0 AFTER paid_amount")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='cash_change'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN cash_change DECIMAL(15,2) NOT NULL DEFAULT 0 AFTER cash_received")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_proof_path'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_proof_path VARCHAR(255) NULL AFTER cash_change")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_approval_required'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_approval_required TINYINT(1) NOT NULL DEFAULT 0 AFTER payment_proof_path")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_verification_status'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_verification_status ENUM('not_required','pending','approved','rejected') NOT NULL DEFAULT 'not_required' AFTER payment_approval_required")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_verified_by'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_verified_by INT NULL AFTER payment_verification_status")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_verified_at'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_verified_at DATETIME NULL AFTER payment_verified_by")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='payment_verification_notes'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN payment_verification_notes VARCHAR(500) NULL AFTER payment_verified_at")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
UPDATE membership_sales SET paid_amount=total_amount WHERE payment_status='paid' AND paid_amount=0 AND total_amount>0;

CREATE TABLE IF NOT EXISTS membership_payment_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,membership_sale_id BIGINT NOT NULL,action_type ENUM('created','proof_uploaded','approved','rejected','paid') NOT NULL,status_before VARCHAR(30) NULL,status_after VARCHAR(30) NULL,proof_path VARCHAR(255) NULL,notes VARCHAR(500) NULL,actor_id INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(membership_sale_id) REFERENCES membership_sales(id) ON DELETE CASCADE,FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_membership_payment_log(membership_sale_id,created_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS public_membership_orders(id BIGINT AUTO_INCREMENT PRIMARY KEY,order_no VARCHAR(60) NOT NULL UNIQUE,request_token CHAR(64) NULL UNIQUE,name VARCHAR(150) NOT NULL,phone VARCHAR(40) NOT NULL,email VARCHAR(150) NULL,address TEXT NULL,member_level_id INT NOT NULL,amount DECIMAL(15,2) NOT NULL DEFAULT 0,payment_method VARCHAR(50) NOT NULL,payment_proof_path VARCHAR(255) NULL,status ENUM('pending','approved','rejected','converted') NOT NULL DEFAULT 'pending',notes TEXT NULL,submitted_ip VARCHAR(64) NULL,reviewed_by INT NULL,reviewed_at DATETIME NULL,member_id INT NULL,membership_sale_id BIGINT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(member_level_id) REFERENCES member_levels(id) ON DELETE RESTRICT,FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL,FOREIGN KEY(membership_sale_id) REFERENCES membership_sales(id) ON DELETE SET NULL,INDEX idx_public_membership_order_status(status,created_at),INDEX idx_public_membership_order_phone(phone)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cms_categories(id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,slug VARCHAR(180) NOT NULL UNIQUE,description TEXT NULL,seo_title VARCHAR(180) NULL,seo_description VARCHAR(320) NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0,created_by INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_cms_category_active(is_active,sort_order)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cms_pages(id BIGINT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,slug VARCHAR(200) NOT NULL UNIQUE,excerpt TEXT NULL,content LONGTEXT NULL,featured_image VARCHAR(255) NULL,seo_title VARCHAR(180) NULL,seo_description VARCHAR(320) NULL,status ENUM('draft','published') NOT NULL DEFAULT 'draft',show_in_sitemap TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0,created_by INT NULL,updated_by INT NULL,published_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_cms_page_status(status,published_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cms_posts(id BIGINT AUTO_INCREMENT PRIMARY KEY,category_id BIGINT NULL,title VARCHAR(180) NOT NULL,slug VARCHAR(200) NOT NULL UNIQUE,excerpt TEXT NULL,content LONGTEXT NULL,featured_image VARCHAR(255) NULL,seo_title VARCHAR(180) NULL,seo_description VARCHAR(320) NULL,status ENUM('draft','published') NOT NULL DEFAULT 'draft',show_in_sitemap TINYINT(1) NOT NULL DEFAULT 1,created_by INT NULL,updated_by INT NULL,published_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(category_id) REFERENCES cms_categories(id) ON DELETE SET NULL,FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,INDEX idx_cms_post_status(status,published_at),INDEX idx_cms_post_category(category_id,status)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cms_menus(id BIGINT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,location ENUM('header','footer') NOT NULL DEFAULT 'header',is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS cms_menu_items(id BIGINT AUTO_INCREMENT PRIMARY KEY,menu_id BIGINT NOT NULL,parent_id BIGINT NULL,label VARCHAR(150) NOT NULL,link_type ENUM('home','page','category','membership','active_members','custom') NOT NULL DEFAULT 'custom',reference_id BIGINT NULL,custom_url VARCHAR(500) NULL,target ENUM('_self','_blank') NOT NULL DEFAULT '_self',sort_order INT NOT NULL DEFAULT 0,is_active TINYINT(1) NOT NULL DEFAULT 1,FOREIGN KEY(menu_id) REFERENCES cms_menus(id) ON DELETE CASCADE,FOREIGN KEY(parent_id) REFERENCES cms_menu_items(id) ON DELETE CASCADE,INDEX idx_cms_menu_items(menu_id,is_active,sort_order)) ENGINE=InnoDB;
INSERT IGNORE INTO cms_menus(id,name,location,is_active) VALUES(1,'Menu Utama','header',1),(2,'Footer','footer',1);
INSERT IGNORE INTO cms_menu_items(id,menu_id,label,link_type,sort_order,is_active) VALUES(1,1,'Beranda','home',10,1),(2,1,'Membership','membership',20,1),(3,1,'Member Teraktif','active_members',30,1);
INSERT IGNORE INTO settings(setting_key,setting_value) VALUES('public_site_enabled','1'),('public_site_title',''),('public_site_description',''),('public_site_home_heading','Prestige Barber Club'),('public_site_home_intro','Pengalaman grooming profesional, nyaman, dan eksklusif.'),('public_site_indexing','1'),('public_active_members_limit','12'),('public_membership_transfer_info',''),('public_membership_ewallet_info','');
-- Kolom membership_sales sebaiknya dibuat melalui upgrade_v4_35_0.php untuk kompatibilitas MySQL/MariaDB lintas versi.

-- Tambah token idempotensi penjualan membership.
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND COLUMN_NAME='request_token'),'SELECT 1',"ALTER TABLE membership_sales ADD COLUMN request_token CHAR(64) NULL AFTER sale_no")); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND INDEX_NAME='uq_membership_request_token'),'SELECT 1','CREATE UNIQUE INDEX uq_membership_request_token ON membership_sales(request_token)')); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- Lengkapi foreign key dan index pembayaran membership secara idempotent.
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA=@db AND TABLE_NAME='membership_sales' AND CONSTRAINT_NAME='fk_membership_payment_verified_by'),'SELECT 1','ALTER TABLE membership_sales ADD CONSTRAINT fk_membership_payment_verified_by FOREIGN KEY(payment_verified_by) REFERENCES users(id) ON DELETE SET NULL')); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
SET @sql=(SELECT IF(EXISTS(SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='membership_sales' AND INDEX_NAME='idx_membership_payment_status'),'SELECT 1','CREATE INDEX idx_membership_payment_status ON membership_sales(payment_status,payment_verification_status,sale_date)')); PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.35.0') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);