CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, username VARCHAR(80) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role ENUM('superadmin','manager','cashier','finance','finance_auditor','barber','employee') NOT NULL DEFAULT 'cashier', phone VARCHAR(40) NULL, email VARCHAR(150) NULL, job_title VARCHAR(120) NULL, avatar VARCHAR(255) NULL, notes TEXT NULL, require_password_change TINYINT(1) NOT NULL DEFAULT 0, password_changed_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, last_login DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS settings ( setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS user_import_batches( id BIGINT AUTO_INCREMENT PRIMARY KEY, file_name VARCHAR(255) NOT NULL, duplicate_mode ENUM('skip','update') NOT NULL DEFAULT 'skip', total_rows INT NOT NULL DEFAULT 0, inserted_rows INT NOT NULL DEFAULT 0, updated_rows INT NOT NULL DEFAULT 0, skipped_rows INT NOT NULL DEFAULT 0, failed_rows INT NOT NULL DEFAULT 0, staff_created INT NOT NULL DEFAULT 0, staff_updated INT NOT NULL DEFAULT 0, imported_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(imported_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_user_import_batches_created(created_at), INDEX idx_user_import_batches_user(imported_by,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS user_import_rows( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_id BIGINT NOT NULL, row_number INT NOT NULL, username VARCHAR(80) NULL, name VARCHAR(120) NULL, role VARCHAR(40) NULL, action_type ENUM('insert','update','skip','error') NOT NULL, status ENUM('success','warning','error') NOT NULL DEFAULT 'success', message VARCHAR(1000) NULL, user_id INT NULL, staff_type ENUM('barber','employee') NULL, staff_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(batch_id) REFERENCES user_import_batches(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_user_import_rows_batch(batch_id,row_number), INDEX idx_user_import_rows_status(batch_id,status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS member_levels ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(80) NOT NULL UNIQUE, sort_order INT NOT NULL DEFAULT 0, card_color VARCHAR(20) NOT NULL DEFAULT '#d4af37', discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0, membership_price DECIMAL(15,2) NOT NULL DEFAULT 0, cashier_bonus_type ENUM('none','fixed','percent') NOT NULL DEFAULT 'none', cashier_bonus_value DECIMAL(15,2) NOT NULL DEFAULT 0, criteria_mode ENUM('all','any') NOT NULL DEFAULT 'all', min_total_spend DECIMAL(15,2) NOT NULL DEFAULT 0, min_transactions INT NOT NULL DEFAULT 0, min_visit_days INT NOT NULL DEFAULT 0, min_points INT NOT NULL DEFAULT 0, min_membership_days INT NOT NULL DEFAULT 0, auto_upgrade TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, description VARCHAR(255) NULL, 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_member_level_order(is_active,sort_order) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS members ( id INT AUTO_INCREMENT PRIMARY KEY, member_code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, phone VARCHAR(40), email VARCHAR(150), address TEXT, instagram VARCHAR(180) NULL, tiktok VARCHAR(180) NULL, facebook VARCHAR(180) NULL, social_media_other VARCHAR(255) NULL, custom_fields_json LONGTEXT NULL, photo_path VARCHAR(255) NULL, gender ENUM('L','P','') NOT NULL DEFAULT '', birth_date DATE NULL, member_level VARCHAR(50) NOT NULL DEFAULT 'Regular', member_level_id INT NULL, member_level_manual TINYINT(1) NOT NULL DEFAULT 0, discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0, points INT NOT NULL DEFAULT 0, card_fee DECIMAL(15,2) NOT NULL DEFAULT 0, card_issued_at DATE NULL, card_expires_at DATE NULL, card_payment_status ENUM('unpaid','paid','waived') NOT NULL DEFAULT 'unpaid', notes TEXT NULL, join_date DATE NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_member_name(name), INDEX idx_member_phone(phone), INDEX idx_member_level_id(member_level_id), FOREIGN KEY(member_level_id) REFERENCES member_levels(id) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS member_level_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY, member_id INT NOT NULL, from_level VARCHAR(80) NULL, to_level VARCHAR(80) NOT NULL, change_type ENUM('automatic','manual') NOT NULL DEFAULT 'automatic', changed_by INT NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE, FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_member_level_history(member_id,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS services ( id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NULL UNIQUE, barcode VARCHAR(120) NULL UNIQUE, category VARCHAR(80) NOT NULL DEFAULT 'Barber Service', name VARCHAR(150) NOT NULL, sort_order INT NOT NULL DEFAULT 0, duration_minutes INT NOT NULL DEFAULT 30, price DECIMAL(15,2) NOT NULL DEFAULT 0, discount_type ENUM('none','percent','fixed') NOT NULL DEFAULT 'none', discount_value DECIMAL(15,2) NOT NULL DEFAULT 0, bonus_amount DECIMAL(15,2) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_services_sort_active(is_active,sort_order,id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS barber_staff ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL UNIQUE, staff_code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(150) NULL, bank_name VARCHAR(120) NULL, bank_code VARCHAR(20) NULL, bank_account_no VARCHAR(50) NULL, bank_account_name VARCHAR(150) NULL, kopra_transfer_method VARCHAR(20) NOT NULL DEFAULT 'BI FAST', photo_path VARCHAR(255) NULL, address TEXT NULL, specialty VARCHAR(150) NULL, join_date DATE NULL, employment_status ENUM('permanent','contract','freelance') NOT NULL DEFAULT 'permanent', bonus_type ENUM('service','percentage','fixed') NOT NULL DEFAULT 'service', bonus_value DECIMAL(15,2) NOT NULL DEFAULT 0, notes TEXT NULL, photo VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_barber_staff_active(is_active,name) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS transactions ( id BIGINT AUTO_INCREMENT PRIMARY KEY, invoice_no VARCHAR(50) NOT NULL UNIQUE, request_token CHAR(64) NULL UNIQUE, member_id INT NULL, customer_name VARCHAR(150) NULL, customer_phone VARCHAR(50) NULL, transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, subtotal_amount DECIMAL(15,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0, total_amount DECIMAL(15,2) NOT NULL DEFAULT 0, tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0, tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0, change_amount DECIMAL(15,2) NOT NULL DEFAULT 0, payment_method VARCHAR(50) NOT NULL, payment_status ENUM('paid','unpaid','partial') NOT NULL DEFAULT 'paid', payment_requested_status ENUM('paid','unpaid','partial') NULL, payment_submitted_amount DECIMAL(15,2) NOT NULL DEFAULT 0, payment_proof_path VARCHAR(255) NULL, payment_proof_required TINYINT(1) NOT NULL DEFAULT 0, payment_approval_required TINYINT(1) NOT NULL DEFAULT 0, payment_verification_status ENUM('not_required','pending','approved','rejected') NOT NULL DEFAULT 'not_required', payment_verified_by INT NULL, payment_verified_at DATETIME NULL, payment_verification_notes VARCHAR(500) NULL, payment_points_applied TINYINT(1) NOT NULL DEFAULT 0, notes TEXT, created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_trx_member FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL, CONSTRAINT fk_trx_user FOREIGN KEY(created_by) REFERENCES users(id), CONSTRAINT fk_transaction_payment_verified_by FOREIGN KEY(payment_verified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_trx_date(transaction_date), INDEX idx_trx_payment(payment_status,payment_method), INDEX idx_trx_verification(payment_verification_status,payment_approval_required), INDEX idx_transactions_verify_date(payment_verification_status,transaction_date), INDEX idx_transactions_customer_phone(customer_phone,transaction_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS invoice_counters( counter_key VARCHAR(80) PRIMARY KEY, last_number INT NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payment_verification_logs( id BIGINT AUTO_INCREMENT PRIMARY KEY, transaction_id BIGINT NOT NULL, action_type ENUM('submitted','proof_uploaded','approved','rejected','reset') 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(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE, FOREIGN KEY(actor_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_payment_verification_transaction(transaction_id,created_at), INDEX idx_payment_verification_action(action_type,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS transaction_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, transaction_id BIGINT NOT NULL, service_id INT NOT NULL, barber_id INT NULL, barber_staff_id INT NULL, qty INT NOT NULL DEFAULT 1, price DECIMAL(15,2) NOT NULL, service_discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0, subtotal DECIMAL(15,2) NOT NULL, bonus_amount DECIMAL(15,2) NOT NULL DEFAULT 0, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE, FOREIGN KEY(service_id) REFERENCES services(id), FOREIGN KEY(barber_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(barber_staff_id) REFERENCES barber_staff(id) ON DELETE SET NULL, INDEX idx_item_barber_staff(barber_staff_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS bonuses ( id BIGINT AUTO_INCREMENT PRIMARY KEY, transaction_id BIGINT NOT NULL, barber_id INT NULL, barber_staff_id INT NULL, amount DECIMAL(15,2) NOT NULL, description VARCHAR(255), status ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid', paid_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE, FOREIGN KEY(barber_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(barber_staff_id) REFERENCES barber_staff(id) ON DELETE SET NULL, INDEX idx_bonus_barber_staff(barber_staff_id), INDEX idx_bonus_status(status,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS finance_categories ( id INT AUTO_INCREMENT PRIMARY KEY, type ENUM('income','expense') NOT NULL, code VARCHAR(40) NULL, name VARCHAR(120) NOT NULL, description VARCHAR(255) 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, UNIQUE KEY uq_finance_category_type_name(type,name), UNIQUE KEY uq_finance_category_type_code(type,code), FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_finance_category(type,is_active,sort_order) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS incomes ( id BIGINT AUTO_INCREMENT PRIMARY KEY, income_date DATE NOT NULL, category_id INT NULL, category VARCHAR(120) NOT NULL, description TEXT NOT NULL, amount DECIMAL(15,2) NOT NULL, payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash', reference_no VARCHAR(100) NULL, source_type VARCHAR(50) NOT NULL DEFAULT 'manual', source_id BIGINT NULL, source_ref VARCHAR(180) NULL, created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(category_id) REFERENCES finance_categories(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id), INDEX idx_income_date(income_date), INDEX idx_income_category(category_id), INDEX idx_income_source(source_type,source_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS membership_sales ( id BIGINT AUTO_INCREMENT PRIMARY KEY, sale_no VARCHAR(60) NOT NULL UNIQUE, request_token CHAR(64) NULL UNIQUE, member_id INT NOT NULL, member_level_id INT NULL, sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, membership_price DECIMAL(15,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0, total_amount DECIMAL(15,2) NOT NULL DEFAULT 0, payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash', payment_status ENUM('unpaid','pending','paid','rejected') NOT NULL DEFAULT 'paid', paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0, cash_received DECIMAL(15,2) NOT NULL DEFAULT 0, cash_change DECIMAL(15,2) NOT NULL DEFAULT 0, payment_proof_path VARCHAR(255) NULL, payment_approval_required TINYINT(1) NOT NULL DEFAULT 0, payment_verification_status ENUM('not_required','pending','approved','rejected') NOT NULL DEFAULT 'not_required', payment_verified_by INT NULL, payment_verified_at DATETIME NULL, payment_verification_notes VARCHAR(500) NULL, cashier_user_id INT NOT NULL, cashier_bonus_type ENUM('none','fixed','percent') NOT NULL DEFAULT 'none', cashier_bonus_value DECIMAL(15,2) NOT NULL DEFAULT 0, cashier_bonus_amount DECIMAL(15,2) NOT NULL DEFAULT 0, bonus_status ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid', bonus_paid_at DATETIME NULL, income_id BIGINT NULL, source_type ENUM('manual','public_registration','pre_member','non_member') NOT NULL DEFAULT 'manual', source_id BIGINT NULL, notes TEXT NULL, status ENUM('paid','void') NOT NULL DEFAULT 'paid', created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, voided_by INT NULL, voided_at DATETIME NULL, FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE RESTRICT, FOREIGN KEY(member_level_id) REFERENCES member_levels(id) ON DELETE SET NULL, FOREIGN KEY(cashier_user_id) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY(income_id) REFERENCES incomes(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(voided_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(payment_verified_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_membership_payment_status(payment_status,payment_verification_status,sale_date), INDEX idx_membership_sale_date(sale_date,status), INDEX idx_membership_sale_cashier(cashier_user_id,sale_date), INDEX idx_membership_bonus_status(bonus_status,status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS expenses ( id BIGINT AUTO_INCREMENT PRIMARY KEY, expense_date DATE NOT NULL, category_id INT NULL, category VARCHAR(100) NOT NULL, description TEXT NOT NULL, amount DECIMAL(15,2) NOT NULL, payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash', evidence_file_1 VARCHAR(255) NULL, evidence_file_2 VARCHAR(255) NULL, source_type VARCHAR(50) NOT NULL DEFAULT 'manual', source_id BIGINT NULL, source_ref VARCHAR(180) NULL, created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(category_id) REFERENCES finance_categories(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id), INDEX idx_expense_date(expense_date), INDEX idx_expense_category(category_id), INDEX idx_expense_source(source_type,source_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS card_design_templates ( id BIGINT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(120) NOT NULL, template_key VARCHAR(80) NOT NULL UNIQUE, template_type ENUM('builtin','custom') NOT NULL DEFAULT 'builtin', front_background VARCHAR(255) NULL, back_background VARCHAR(255) NULL, primary_color VARCHAR(20) NULL, accent_color VARCHAR(20) NULL, text_color VARCHAR(20) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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_card_template_active(is_active,template_type) ) ENGINE=InnoDB; INSERT IGNORE INTO card_design_templates(template_name,template_key,template_type,primary_color,accent_color,text_color,is_active) VALUES ('Prestige Dark','prestige_dark','builtin','#172554','#d4af37','#ffffff',1), ('Royal Gold','royal_gold','builtin','#21170b','#d6ad60','#fff8e7',1), ('Minimal Light','minimal_light','builtin','#f8fafc','#172554','#111827',1), ('Barber Classic','barber_classic','builtin','#7f1d1d','#f5f5f4','#ffffff',1), ('Midnight Blue','midnight_blue','builtin','#020617','#38bdf8','#f8fafc',1); CREATE TABLE IF NOT EXISTS backup_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY, backup_type ENUM('database','full_data') NOT NULL, file_name VARCHAR(255) NOT NULL, file_size BIGINT NOT NULL DEFAULT 0, checksum_sha256 VARCHAR(64) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_backup_created(created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS employee_staff ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL UNIQUE, employee_code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(150) NULL, bank_name VARCHAR(120) NULL, bank_code VARCHAR(20) NULL, bank_account_no VARCHAR(50) NULL, bank_account_name VARCHAR(150) NULL, kopra_transfer_method VARCHAR(20) NOT NULL DEFAULT 'BI FAST', job_title VARCHAR(100) NULL, employment_status ENUM('Tetap','Kontrak','Freelance','Harian') NOT NULL DEFAULT 'Tetap', join_date DATE NULL, base_salary DECIMAL(15,2) NOT NULL DEFAULT 0, notes TEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_employee_name(name), INDEX idx_employee_active(is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS employee_earnings ( id BIGINT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, earning_date DATE NOT NULL, earning_type ENUM('salary','commission','bonus','overtime','allowance','other') NOT NULL DEFAULT 'salary', amount DECIMAL(15,2) NOT NULL DEFAULT 0, description VARCHAR(255) NULL, status ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid', paid_at DATETIME NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(employee_id) REFERENCES employee_staff(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_employee_earning_date(earning_date), INDEX idx_employee_earning_staff(employee_id,status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS attendance_devices ( id INT AUTO_INCREMENT PRIMARY KEY, device_name VARCHAR(150) NOT NULL, device_code VARCHAR(80) NOT NULL UNIQUE, api_token VARCHAR(128) NOT NULL UNIQUE, device_type ENUM('card','fingerprint','face_id','hybrid') NOT NULL DEFAULT 'hybrid', location_name VARCHAR(150) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_att_device_active(is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS attendance_records ( id BIGINT AUTO_INCREMENT PRIMARY KEY, staff_type ENUM('barber','employee') NOT NULL, staff_id INT NOT NULL, attendance_date DATE NOT NULL, check_in DATETIME NULL, check_out DATETIME NULL, check_in_method ENUM('card','fingerprint','face_id','manual') NULL, check_in_photo VARCHAR(255) NULL, check_in_lat DECIMAL(10,7) NULL, check_in_lng DECIMAL(10,7) NULL, check_in_accuracy DECIMAL(10,2) NULL, check_out_method ENUM('card','fingerprint','face_id','manual') NULL, check_out_photo VARCHAR(255) NULL, check_out_lat DECIMAL(10,7) NULL, check_out_lng DECIMAL(10,7) NULL, check_out_accuracy DECIMAL(10,2) NULL, device_id INT NULL, notes VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES attendance_devices(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uq_attendance_staff_day(staff_type,staff_id,attendance_date), INDEX idx_attendance_date(attendance_date), INDEX idx_attendance_staff(staff_type,staff_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hardware_profiles ( id INT AUTO_INCREMENT PRIMARY KEY, hardware_type ENUM('fingerprint','attendance','qr_barcode_scanner','card_printer','receipt_printer','other') NOT NULL, brand VARCHAR(100) NULL, model VARCHAR(150) NOT NULL, connection_type VARCHAR(80) NULL, driver_name VARCHAR(180) NULL, paper_width_mm INT NULL, print_protocol VARCHAR(80) NULL, bluetooth_profile VARCHAR(80) NULL, driver_file VARCHAR(255) NULL, official_driver_url VARCHAR(500) NULL, install_notes TEXT NULL, device_code VARCHAR(100) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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_hardware_type(hardware_type,is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS hardware_install_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, hardware_id INT NULL, action_type ENUM('download_driver','open_official_url','test_print','test_scan','test_attendance','other') NOT NULL DEFAULT 'other', notes VARCHAR(255) NULL, user_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(hardware_id) REFERENCES hardware_profiles(id) ON DELETE SET NULL, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_hardware_log_created(created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS customer_display_state ( user_id INT PRIMARY KEY, state_json LONGTEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS accounting_accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_code VARCHAR(30) NOT NULL UNIQUE, account_name VARCHAR(150) NOT NULL, account_type ENUM('asset','liability','equity','revenue','expense') NOT NULL, normal_balance ENUM('debit','credit') NOT NULL, parent_id INT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(parent_id) REFERENCES accounting_accounts(id) ON DELETE SET NULL, INDEX idx_account_type(account_type,is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS journal_entries ( id BIGINT AUTO_INCREMENT PRIMARY KEY, journal_date DATE NOT NULL, reference_no VARCHAR(80) NULL, description VARCHAR(255) NOT NULL, source_type VARCHAR(50) NULL, source_id BIGINT NULL, status ENUM('draft','posted','void') NOT NULL DEFAULT 'posted', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_journal_date(journal_date), INDEX idx_journal_source(source_type,source_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS journal_lines ( id BIGINT AUTO_INCREMENT PRIMARY KEY, journal_entry_id BIGINT NOT NULL, account_id INT NOT NULL, debit DECIMAL(15,2) NOT NULL DEFAULT 0, credit DECIMAL(15,2) NOT NULL DEFAULT 0, memo VARCHAR(255) NULL, FOREIGN KEY(journal_entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE, FOREIGN KEY(account_id) REFERENCES accounting_accounts(id), INDEX idx_journal_account(account_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS tax_profiles ( id INT AUTO_INCREMENT PRIMARY KEY, tax_name VARCHAR(100) NOT NULL, tax_code VARCHAR(40) NULL, tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0, tax_type ENUM('sales','withholding','other') NOT NULL DEFAULT 'sales', inclusive TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payroll_periods ( id BIGINT AUTO_INCREMENT PRIMARY KEY, period_name VARCHAR(120) NOT NULL, period_type ENUM('daily','weekly','monthly','custom') NOT NULL DEFAULT 'monthly', date_from DATE NOT NULL, date_to DATE NOT NULL, status ENUM('draft','approved','paid','closed') NOT NULL DEFAULT 'draft', notes TEXT NULL, created_by INT NULL, approved_by INT NULL, approved_at DATETIME NULL, paid_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_payroll_period(date_from,date_to,status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payroll_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, payroll_period_id BIGINT NOT NULL, staff_type ENUM('barber','employee') NOT NULL, staff_id INT NOT NULL, base_salary DECIMAL(15,2) NOT NULL DEFAULT 0, daily_salary DECIMAL(15,2) NOT NULL DEFAULT 0, attendance_days INT NOT NULL DEFAULT 0, service_bonus DECIMAL(15,2) NOT NULL DEFAULT 0, manual_bonus DECIMAL(15,2) NOT NULL DEFAULT 0, overtime DECIMAL(15,2) NOT NULL DEFAULT 0, allowance DECIMAL(15,2) NOT NULL DEFAULT 0, deduction DECIMAL(15,2) NOT NULL DEFAULT 0, tax_deduction DECIMAL(15,2) NOT NULL DEFAULT 0, gross_amount DECIMAL(15,2) NOT NULL DEFAULT 0, net_amount DECIMAL(15,2) NOT NULL DEFAULT 0, payment_status ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid', paid_at DATETIME NULL, notes VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE, UNIQUE KEY uq_payroll_staff_period(payroll_period_id,staff_type,staff_id), INDEX idx_payroll_item_status(payment_status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS kopra_transfer_batches ( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_no VARCHAR(60) NOT NULL UNIQUE, batch_name VARCHAR(180) NOT NULL, payroll_period_id BIGINT NULL, debit_account_no VARCHAR(35) NOT NULL, transfer_date DATE NOT NULL, purpose_code VARCHAR(4) NOT NULL DEFAULT '1', beneficiary_type VARCHAR(3) NOT NULL DEFAULT '1', beneficiary_resident CHAR(1) NOT NULL DEFAULT 'Y', beneficiary_citizen CHAR(1) NOT NULL DEFAULT 'Y', status ENUM('draft','exported','paid','cancelled') NOT NULL DEFAULT 'draft', total_items INT NOT NULL DEFAULT 0, total_amount DECIMAL(18,2) NOT NULL DEFAULT 0, exported_at DATETIME NULL, paid_at DATETIME NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(payroll_period_id) REFERENCES payroll_periods(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_kopra_batch_status(status,transfer_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS kopra_transfer_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_id BIGINT NOT NULL, payroll_item_id BIGINT NULL, staff_type ENUM('barber','employee','manual') NOT NULL DEFAULT 'manual', staff_id INT NULL, destination_bank_name VARCHAR(120) NOT NULL, destination_bank_code VARCHAR(20) NULL, destination_account_no VARCHAR(50) NOT NULL, destination_account_name VARCHAR(150) NOT NULL, destination_address VARCHAR(105) NULL, transfer_method ENUM('In-House','BI FAST','Online','SKN','RTGS') NOT NULL DEFAULT 'BI FAST', amount DECIMAL(18,2) NOT NULL, transaction_reference VARCHAR(19) NULL, remark VARCHAR(200) NULL, status ENUM('pending','exported','paid','cancelled') NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(batch_id) REFERENCES kopra_transfer_batches(id) ON DELETE CASCADE, FOREIGN KEY(payroll_item_id) REFERENCES payroll_items(id) ON DELETE SET NULL, UNIQUE KEY uq_kopra_batch_payroll(batch_id,payroll_item_id), INDEX idx_kopra_item_batch_status(batch_id,status), INDEX idx_kopra_item_payroll(payroll_item_id) ) ENGINE=InnoDB; INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'card_printer','Pointman','Nuvia N15','USB 2.0 / 10/100 Ethernet','Pointman Nuvia N15 Windows Driver','https://www.pointman.co.kr/ENG/product/CARD-PRINTER/nuvia-N15.php','Preset resmi aplikasi. Gunakan kartu PVC CR80 85.60 x 53.98 mm. Instal driver Pointman di PC kasir, lalu pilih Pointman Nuvia N15 pada dialog Print Windows. Gunakan ukuran 100% / Actual Size, tanpa scaling.','POINTMAN-N15',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Pointman' AND model='Nuvia N15'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'attendance','ZKTeco','SpeedFace-V5L','TCP/IP / ADMS PUSH','ZKTeco ZKBio / PUSH integration','https://www.zkteco.com/en/SpeedFaceSeries/SpeedFace-V5L-Series','Preset resmi aplikasi. Set User ID pada mesin sama dengan staff_code/employee_code aplikasi. Untuk sinkron server, arahkan PUSH/ADMS ke endpoint zkteco_push.php sesuai konfigurasi perangkat.','ZK-V5L',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='ZKTeco' AND model='SpeedFace-V5L'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'receipt_printer','Epson','TM-T82III LAN','Ethernet LAN / ESC-POS','Epson Advanced Printer Driver / TM Utility','https://support.epson.net/publist/bsmanual.php?lang=EN&model=TM-T82III','Preset resmi aplikasi. Sambungkan LAN, atur IP melalui Epson TM-T82III Utility/EpsonNet Config, instal driver di PC kasir, lalu gunakan Cetak Thermal 80mm. Mode browser/OS driver adalah default paling stabil untuk hosting cloud.','EPSON-TM-T82III',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Epson' AND model='TM-T82III LAN'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'qr_barcode_scanner','Newland AIDC','HR3280 / HR32 USB 2D','USB HID Keyboard / USB-COM','Newland EasySet / USB-COM Driver','https://www.newlandaidc.com/roa/products/HandheldScanner/HR32.html','Preset resmi aplikasi. Mode yang disarankan USB HID Keyboard (KBW), suffix Enter/CR aktif. Cukup colok USB lalu buka Scan Member. USB-COM tersedia jika ingin mode serial.','NEWLAND-HR3280',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Newland AIDC' AND model='HR3280 / HR32 USB 2D'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'qr_barcode_scanner','Generic','USB HID 2D Scanner','USB HID Keyboard','Plug and Play / OS HID','', 'Untuk scanner 1D/2D generik yang bekerja sebagai keyboard. Set suffix Enter/CR agar scan langsung diproses.','GENERIC-USB-HID-2D',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Generic' AND model='USB HID 2D Scanner'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'qr_barcode_scanner','Generic','Bluetooth HID 2D Scanner','Bluetooth HID Keyboard','Bluetooth HID / Plug and Play','', 'Pair ke komputer/tablet sebagai keyboard Bluetooth. Set suffix Enter/CR. Cocok untuk penggunaan mobile/kasir tanpa kabel.','GENERIC-BT-HID-2D',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Generic' AND model='Bluetooth HID 2D Scanner'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'card_printer','Generic','CR80 ID Card Printer','USB / Ethernet','Vendor OS Driver','', 'Printer kartu generik CR80. Gunakan driver vendor pada OS dan ukuran 85.60 x 53.98 mm, 100% tanpa scaling.','GENERIC-CR80',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Generic' AND model='CR80 ID Card Printer'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'attendance','Generic','HTTP API Attendance Device','TCP/IP / HTTP API','Vendor API / Middleware','', 'Mesin absensi generik yang dapat mengirim staff code/user ID melalui HTTP API atau middleware ke attendance_device_api.php.','GENERIC-ATT-API',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Generic' AND model='HTTP API Attendance Device'); INSERT INTO hardware_profiles(hardware_type,brand,model,connection_type,driver_name,official_driver_url,install_notes,device_code,is_active,created_by) SELECT 'receipt_printer','Generic','ESC-POS 80mm Thermal','USB / LAN / Bluetooth','ESC-POS / Vendor OS Driver','', 'Printer thermal generik ESC/POS 80 mm. Gunakan driver OS/browser print atau Local Agent untuk integrasi ESC/POS.','GENERIC-ESCPOS-80',1,NULL WHERE NOT EXISTS (SELECT 1 FROM hardware_profiles WHERE brand='Generic' AND model='ESC-POS 80mm Thermal'); CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(60) NOT NULL UNIQUE, barcode VARCHAR(120) NULL UNIQUE, name VARCHAR(180) NOT NULL, category VARCHAR(100) NULL, usage_type ENUM('retail','operational','both') NOT NULL DEFAULT 'retail', description TEXT NULL, cost_price DECIMAL(15,2) NOT NULL DEFAULT 0, sell_price DECIMAL(15,2) NOT NULL DEFAULT 0, cashier_bonus_type ENUM('none','fixed','percent') NOT NULL DEFAULT 'none', cashier_bonus_value DECIMAL(15,2) NOT NULL DEFAULT 0, stock_qty INT NOT NULL DEFAULT 0, min_stock INT NOT NULL DEFAULT 0, unit VARCHAR(40) NOT NULL DEFAULT 'pcs', image_path VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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_product_name(name), INDEX idx_product_stock(stock_qty,min_stock), INDEX idx_product_barcode(barcode) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS transaction_product_items ( id BIGINT AUTO_INCREMENT PRIMARY KEY, transaction_id BIGINT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL DEFAULT 1, price DECIMAL(15,2) NOT NULL, cost_price DECIMAL(15,2) NOT NULL DEFAULT 0, subtotal DECIMAL(15,2) NOT NULL, cashier_bonus_amount DECIMAL(15,2) NOT NULL DEFAULT 0, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id), INDEX idx_tpi_product(product_id), INDEX idx_tpi_transaction(transaction_id) ) ENGINE=InnoDB; \nCREATE TABLE IF NOT EXISTS non_member_customers( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, phone VARCHAR(50) NOT NULL, normalized_phone VARCHAR(50) NOT NULL UNIQUE, first_transaction_id BIGINT NULL, last_transaction_id BIGINT NULL, visit_count INT NOT NULL DEFAULT 0, total_spend DECIMAL(15,2) NOT NULL DEFAULT 0, first_visit_at DATETIME NULL, last_visit_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(first_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL, FOREIGN KEY(last_transaction_id) REFERENCES transactions(id) ON DELETE SET NULL, INDEX idx_non_member_name(name), INDEX idx_non_member_last(last_visit_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS product_cashier_bonuses( id BIGINT AUTO_INCREMENT PRIMARY KEY, transaction_id BIGINT NOT NULL, product_item_id BIGINT NOT NULL, product_id INT NOT NULL, cashier_user_id INT NOT NULL, amount DECIMAL(15,2) NOT NULL DEFAULT 0, bonus_status ENUM('pending','unpaid','paid','void') NOT NULL DEFAULT 'pending', paid_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE CASCADE, FOREIGN KEY(product_item_id) REFERENCES transaction_product_items(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id), FOREIGN KEY(cashier_user_id) REFERENCES users(id), UNIQUE KEY uq_product_cashier_bonus_item(product_item_id), INDEX idx_product_cashier_bonus_cashier(cashier_user_id,bonus_status,created_at), INDEX idx_product_cashier_bonus_trx(transaction_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS user_roles( user_id INT NOT NULL, role ENUM('superadmin','manager','cashier','finance','finance_auditor','barber','employee') NOT NULL, assigned_by INT NULL, assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(user_id,role), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(assigned_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_user_roles_role(role,user_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS service_product_usage_rules ( id BIGINT AUTO_INCREMENT PRIMARY KEY, service_id INT NOT NULL, product_id INT NOT NULL, qty_per_service INT NOT NULL DEFAULT 1, is_active TINYINT(1) NOT NULL DEFAULT 1, notes VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uq_service_product_usage(service_id,product_id), INDEX idx_usage_rule_product(product_id,is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS product_operational_usages ( id BIGINT AUTO_INCREMENT PRIMARY KEY, usage_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, product_id INT NOT NULL, service_id INT NULL, transaction_id BIGINT NULL, barber_staff_id INT NULL, qty INT NOT NULL DEFAULT 1, unit_cost DECIMAL(15,2) NOT NULL DEFAULT 0, total_cost DECIMAL(15,2) NOT NULL DEFAULT 0, usage_source ENUM('manual','service_auto') NOT NULL DEFAULT 'manual', notes VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(product_id) REFERENCES products(id), FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE SET NULL, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE SET NULL, FOREIGN KEY(barber_staff_id) REFERENCES barber_staff(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_operational_usage_date(usage_date), INDEX idx_operational_usage_product(product_id,usage_date), INDEX idx_operational_usage_service(service_id,usage_date), INDEX idx_operational_usage_transaction(transaction_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS stock_movements ( id BIGINT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, movement_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, movement_type ENUM('opening','purchase','sale','operational_use','adjustment_in','adjustment_out','return_in','return_out') NOT NULL, qty_change INT NOT NULL, stock_before INT NOT NULL DEFAULT 0, stock_after INT NOT NULL DEFAULT 0, reference_type VARCHAR(50) NULL, reference_id BIGINT NULL, notes VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_stock_product_date(product_id,movement_date), INDEX idx_stock_reference(reference_type,reference_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS renovation_projects ( id BIGINT AUTO_INCREMENT PRIMARY KEY, project_name VARCHAR(180) NOT NULL, location VARCHAR(255) NULL, planned_start DATE NULL, planned_end DATE NULL, actual_start DATE NULL, actual_end DATE NULL, budget DECIMAL(15,2) NOT NULL DEFAULT 0, status ENUM('planning','execution','finishing','completed','on_hold') NOT NULL DEFAULT 'planning', progress_percent DECIMAL(5,2) NOT NULL DEFAULT 0, description TEXT NULL, 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_renov_status(status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS renovation_project_history ( id BIGINT AUTO_INCREMENT PRIMARY KEY, project_id BIGINT NOT NULL, change_type VARCHAR(50) NOT NULL DEFAULT 'update', field_name VARCHAR(80) NOT NULL, old_value TEXT NULL, new_value TEXT NULL, changed_by INT NULL, changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(project_id) REFERENCES renovation_projects(id) ON DELETE CASCADE, FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_renov_history_project(project_id,changed_at), INDEX idx_renov_history_field(field_name) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS renovation_tasks ( id BIGINT AUTO_INCREMENT PRIMARY KEY, project_id BIGINT NOT NULL, phase ENUM('planning','execution','finishing') NOT NULL, task_name VARCHAR(200) NOT NULL, description TEXT NULL, planned_date DATE NULL, completed_date DATE NULL, progress_percent DECIMAL(5,2) NOT NULL DEFAULT 0, status ENUM('not_started','in_progress','done','blocked') NOT NULL DEFAULT 'not_started', sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(project_id) REFERENCES renovation_projects(id) ON DELETE CASCADE, INDEX idx_renov_task(project_id,phase,status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS renovation_expenses ( id BIGINT AUTO_INCREMENT PRIMARY KEY, project_id BIGINT NOT NULL, task_id BIGINT NULL, category_source ENUM('phase','task','custom') NOT NULL DEFAULT 'custom', global_expense_id BIGINT NULL, expense_date DATE NOT NULL, category VARCHAR(120) NULL, description VARCHAR(255) NOT NULL, amount DECIMAL(15,2) NOT NULL DEFAULT 0, payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash', evidence_file_1 VARCHAR(255) NULL, evidence_file_2 VARCHAR(255) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(project_id) REFERENCES renovation_projects(id) ON DELETE CASCADE, FOREIGN KEY(task_id) REFERENCES renovation_tasks(id) ON DELETE SET NULL, FOREIGN KEY(global_expense_id) REFERENCES expenses(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_renov_expense(project_id,expense_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS renovation_photos ( id BIGINT AUTO_INCREMENT PRIMARY KEY, project_id BIGINT NOT NULL, task_id BIGINT NULL, phase ENUM('planning','execution','finishing') NOT NULL, photo_date DATE NOT NULL, title VARCHAR(180) NULL, caption TEXT NULL, photo_path VARCHAR(255) NOT NULL, progress_percent DECIMAL(5,2) NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(project_id) REFERENCES renovation_projects(id) ON DELETE CASCADE, FOREIGN KEY(task_id) REFERENCES renovation_tasks(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_renov_photo(project_id,phase,photo_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS member_memories ( id BIGINT AUTO_INCREMENT PRIMARY KEY, member_id INT NOT NULL, transaction_id BIGINT NULL, memory_type ENUM('member_profile','visit') NOT NULL DEFAULT 'member_profile', memory_date DATE NULL, title VARCHAR(180) NOT NULL, caption TEXT NULL, photo_path VARCHAR(255) NOT NULL, is_featured TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE, FOREIGN KEY(transaction_id) REFERENCES transactions(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_memory_member(member_id,memory_date), INDEX idx_memory_type(memory_type,transaction_id), INDEX idx_memory_featured(is_featured,is_active) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS announcements ( id BIGINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, body TEXT NOT NULL, announcement_type ENUM('info','success','warning','danger','primary') NOT NULL DEFAULT 'info', show_login TINYINT(1) NOT NULL DEFAULT 1, show_dashboard TINYINT(1) NOT NULL DEFAULT 1, show_all_pages TINYINT(1) NOT NULL DEFAULT 0, start_at DATETIME NULL, end_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, updated_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, FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_announcement_active(is_active,start_at,end_at), INDEX idx_announcement_location(show_login,show_dashboard,show_all_pages) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_devices ( id INT AUTO_INCREMENT PRIMARY KEY, device_name VARCHAR(150) NOT NULL, serial_number VARCHAR(120) NOT NULL UNIQUE, location_name VARCHAR(150) NULL, ip_address VARCHAR(64) NULL, adms_port INT NOT NULL DEFAULT 80, push_path VARCHAR(180) NOT NULL DEFAULT '/iclock/cdata', device_model VARCHAR(100) NOT NULL DEFAULT 'SpeedFace-V5L', firmware_version VARCHAR(100) NULL, install_status ENUM('draft','configured','connected','synced','error') NOT NULL DEFAULT 'draft', server_mode ENUM('domain','ip') NOT NULL DEFAULT 'domain', server_address VARCHAR(190) NULL, server_port INT NOT NULL DEFAULT 443, use_https TINYINT(1) NOT NULL DEFAULT 1, device_type VARCHAR(60) NOT NULL DEFAULT 'TA Push', is_active TINYINT(1) NOT NULL DEFAULT 1, last_seen_at DATETIME NULL, last_options_at DATETIME NULL, last_attlog_at DATETIME NULL, last_protocol_event VARCHAR(80) NULL, last_ip VARCHAR(64) NULL, last_user_agent VARCHAR(255) NULL, 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_zk_device_active(is_active,last_seen_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_install_sessions( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NOT NULL, serial_number VARCHAR(120) NOT NULL, expected_host VARCHAR(190) NOT NULL, expected_port INT NOT NULL DEFAULT 443, expected_https TINYINT(1) NOT NULL DEFAULT 1, expected_domain_mode TINYINT(1) NOT NULL DEFAULT 1, local_ip VARCHAR(64) NULL, subnet_mask VARCHAR(64) NULL, gateway VARCHAR(64) NULL, dns_server VARCHAR(64) NULL, install_step INT NOT NULL DEFAULT 1, endpoint_test_status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending', endpoint_test_detail VARCHAR(500) NULL, first_push_at DATETIME NULL, final_sync_at DATETIME NULL, status ENUM('draft','waiting_device','connected','synced','failed') NOT NULL DEFAULT 'draft', created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_zk_install_device(device_id,status), INDEX idx_zk_install_sn(serial_number) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_connection_checks( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NULL, serial_number VARCHAR(120) NULL, check_type VARCHAR(50) NOT NULL, status ENUM('success','warning','failed','info') NOT NULL DEFAULT 'info', target VARCHAR(255) NULL, latency_ms INT NULL, detail VARCHAR(1000) NULL, checked_by INT NULL, checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE SET NULL, FOREIGN KEY(checked_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_zk_check_device(device_id,checked_at), INDEX idx_zk_check_status(status,checked_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_step_diagnostics( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NOT NULL, serial_number VARCHAR(120) NOT NULL, step_no INT NOT NULL, step_key VARCHAR(60) NOT NULL, status ENUM('pending','success','warning','failed','info') NOT NULL DEFAULT 'pending', target VARCHAR(255) NULL, detail VARCHAR(1000) NULL, checked_by INT NULL, checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE CASCADE, FOREIGN KEY(checked_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_zk_step_device(device_id,step_no,checked_at), INDEX idx_zk_step_status(status,checked_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_user_mappings ( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NULL, device_user_id VARCHAR(80) NOT NULL, staff_type ENUM('barber','employee') NOT NULL, staff_id INT NOT NULL, display_name VARCHAR(180) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uq_zk_mapping(device_id,device_user_id), INDEX idx_zk_mapping_staff(staff_type,staff_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_attendance_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NULL, serial_number VARCHAR(120) NULL, device_user_id VARCHAR(80) NOT NULL, punch_time DATETIME NOT NULL, verify_code INT NULL, verify_method ENUM('face_id','fingerprint','card','unknown') NOT NULL DEFAULT 'unknown', attendance_action VARCHAR(40) NULL, staff_type ENUM('barber','employee') NULL, staff_id INT NULL, attendance_record_id BIGINT NULL, raw_line TEXT NULL, process_status ENUM('processed','unmapped','duplicate','error') NOT NULL DEFAULT 'processed', process_message VARCHAR(255) NULL, received_ip VARCHAR(64) NULL, received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE SET NULL, FOREIGN KEY(attendance_record_id) REFERENCES attendance_records(id) ON DELETE SET NULL, INDEX idx_zk_attlog_user(device_user_id,punch_time), INDEX idx_zk_attlog_device(device_id,received_at), INDEX idx_zk_attlog_status(process_status,received_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS zk_sync_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, device_id INT NULL, serial_number VARCHAR(120) NULL, direction ENUM('device_to_server','server_to_device') NOT NULL DEFAULT 'device_to_server', event_type VARCHAR(80) NOT NULL, status ENUM('success','warning','error') NOT NULL DEFAULT 'success', processed_count INT NOT NULL DEFAULT 0, unknown_count INT NOT NULL DEFAULT 0, detail TEXT NULL, remote_ip VARCHAR(64) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(device_id) REFERENCES zk_devices(id) ON DELETE SET NULL, INDEX idx_zk_sync_device(device_id,created_at), INDEX idx_zk_sync_status(status,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS pre_member_import_batches ( id BIGINT AUTO_INCREMENT PRIMARY KEY, file_name VARCHAR(255) NOT NULL, total_rows INT NOT NULL DEFAULT 0, inserted_rows INT NOT NULL DEFAULT 0, duplicate_rows INT NOT NULL DEFAULT 0, skipped_rows INT NOT NULL DEFAULT 0, imported_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(imported_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_pre_member_batch_created(created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS pre_members ( id BIGINT AUTO_INCREMENT PRIMARY KEY, import_batch_id BIGINT NULL, source_file VARCHAR(255) NOT NULL, row_number INT NULL, external_code VARCHAR(100) NULL, name VARCHAR(150) NOT NULL, phone VARCHAR(40) NULL, email VARCHAR(150) NULL, address TEXT NULL, instagram VARCHAR(180) NULL, tiktok VARCHAR(180) NULL, facebook VARCHAR(180) NULL, social_media_other VARCHAR(255) NULL, gender ENUM('L','P','') NOT NULL DEFAULT '', birth_date DATE NULL, join_date DATE NULL, membership_validity_months INT NOT NULL DEFAULT 12, notes TEXT NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', reviewed_by INT NULL, reviewed_at DATETIME NULL, review_notes TEXT NULL, approved_member_id INT NULL, imported_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(import_batch_id) REFERENCES pre_member_import_batches(id) ON DELETE SET NULL, FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(approved_member_id) REFERENCES members(id) ON DELETE SET NULL, FOREIGN KEY(imported_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_pre_member_status(status,created_at), INDEX idx_pre_member_phone(phone), INDEX idx_pre_member_email(email), INDEX idx_pre_member_batch(import_batch_id) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS pre_member_approval_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, pre_member_id BIGINT NOT NULL, action ENUM('approved','rejected') NOT NULL, reviewer_id INT NULL, review_notes TEXT NULL, member_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(pre_member_id) REFERENCES pre_members(id) ON DELETE CASCADE, FOREIGN KEY(reviewer_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL, INDEX idx_pre_member_approval(pre_member_id,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS activity_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, action VARCHAR(80) NOT NULL, detail TEXT, ip_address VARCHAR(64), user_agent VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_log_created(created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS impersonation_logs( id BIGINT AUTO_INCREMENT PRIMARY KEY, actor_user_id INT NULL, target_user_id INT NULL, actor_username VARCHAR(80) NOT NULL, actor_name VARCHAR(120) NOT NULL, target_username VARCHAR(80) NOT NULL, target_name VARCHAR(120) NOT NULL, target_role VARCHAR(40) NOT NULL, session_token CHAR(64) NOT NULL UNIQUE, status ENUM('active','ended') NOT NULL DEFAULT 'active', start_ip VARCHAR(64) NULL, end_ip VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ended_at DATETIME NULL, end_reason ENUM('returned','logout','forced') NULL, FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(target_user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_impersonation_actor(actor_user_id,started_at), INDEX idx_impersonation_target(target_user_id,started_at), INDEX idx_impersonation_status(status,started_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS import_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, file_name VARCHAR(255) NOT NULL, total_rows INT NOT NULL DEFAULT 0, inserted_rows INT NOT NULL DEFAULT 0, updated_rows INT NOT NULL DEFAULT 0, skipped_rows INT NOT NULL DEFAULT 0, imported_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(imported_by) REFERENCES users(id) ) ENGINE=InnoDB; INSERT IGNORE INTO users(name,username,password,role,is_active) VALUES ('Super Administrator','admin','$2y$12$HhUhxLEQ21hocSFNqh294uUHA.SIsgGoK3Rf4EaqUZg2d6gCqv5bW','superadmin',1); INSERT IGNORE INTO member_levels(code,name,sort_order,card_color,discount_percent,membership_price,cashier_bonus_type,cashier_bonus_value,criteria_mode,min_total_spend,min_transactions,min_visit_days,min_points,min_membership_days,auto_upgrade,is_active,description) VALUES ('REG','Regular',10,'#94a3b8',0,0,'none',0,'all',0,0,0,0,0,1,1,'Tingkatan awal member'), ('SILVER','Silver',20,'#c0c0c0',5,0,'none',0,'all',500000,5,0,100,30,1,1,'Member aktif dengan transaksi rutin'), ('GOLD','Gold',30,'#d4af37',8,0,'none',0,'all',1000000,7,0,30,60,1,1,'Member loyal dengan benefit lebih tinggi'), ('PLATINUM','Platinum',40,'#8b9dc3',10,0,'none',0,'all',1500000,10,0,50,90,1,1,'Tingkatan eksklusif; dapat menggunakan approval admin'); UPDATE members m LEFT JOIN member_levels l ON l.name=m.member_level SET m.member_level_id=l.id WHERE m.member_level_id IS NULL; INSERT INTO settings(setting_key,setting_value) VALUES ('brand_name','Prestige Barber Club'), ('brand_tagline','Professional Grooming Management System'), ('brand_address','Alamat Barbershop'), ('brand_phone','08xxxxxxxxxx'), ('brand_email','info@barbershop.local'), ('brand_logo',''), ('brand_primary','#172554'), ('brand_accent','#d4af37'), ('dashboard_monthly_target','0'), ('invoice_prefix','TRX'), ('whatsapp_country_code','62'), ('thermal_paper','80'), ('point_divisor','10000'), ('footer_text','Terima kasih telah berkunjung. Sampai jumpa kembali.'), ('login_layout','split'), ('login_title','Selamat Datang Kembali'), ('login_subtitle','Kelola operasional barbershop secara cepat, aman, dan terintegrasi dalam satu sistem.'), ('login_badge','BARBERSHOP MANAGEMENT SYSTEM'), ('login_footer','Sistem operasional terpadu untuk pelayanan yang lebih profesional.'), ('login_background',''), ('login_show_features','1'), ('login_show_contact','1') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); INSERT INTO settings(setting_key,setting_value) VALUES ('member_card_default_fee','0'), ('member_card_validity_months','12'), ('qris_image',''), ('qris_name','QRIS'), ('attendance_auto_checkout_hours','12') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO settings(setting_key,setting_value) VALUES ('customer_display_enabled','1'), ('attendance_photo_enabled','1'), ('attendance_photo_required','0'), ('default_tax_enabled','0'), ('default_tax_rate','0'), ('default_tax_inclusive','0') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT IGNORE INTO accounting_accounts(account_code,account_name,account_type,normal_balance) VALUES ('1001','Kas','asset','debit'), ('1002','Bank / QRIS','asset','debit'), ('1101','Piutang Usaha','asset','debit'), ('2001','Utang Usaha','liability','credit'), ('2101','Utang Pajak','liability','credit'), ('3001','Modal Pemilik','equity','credit'), ('3101','Saldo Laba','equity','credit'), ('4001','Pendapatan Jasa','revenue','credit'), ('4002','Pendapatan Lain','revenue','credit'), ('5001','Beban Operasional','expense','debit'), ('5002','Beban Gaji dan Bonus','expense','debit'), ('5003','Beban Pajak','expense','debit'), ('1201','Persediaan Produk','asset','debit'), ('4003','Pendapatan Produk','revenue','credit'), ('5101','Harga Pokok Penjualan Produk','expense','debit'); INSERT INTO settings(setting_key,setting_value) VALUES ('selected_card_printer','Pointman Nuvia N15'), ('selected_attendance_device','ZKTeco SpeedFace-V5L'), ('selected_receipt_printer','Epson TM-T82III LAN'), ('selected_member_scanner','Newland AIDC HR3280 / HR32 USB 2D'), ('epson_tm_t82_ip',''), ('zkteco_push_enabled','1') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO settings(setting_key,setting_value) VALUES ('selected_member_scanner_id',''), ('member_scanner_mode','hid_keyboard'), ('member_scanner_suffix','enter'), ('member_scanner_idle_ms','120') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO settings(setting_key,setting_value) VALUES ('selected_card_printer_id',''), ('card_printer_mode','os_driver'), ('card_printer_connection','auto'), ('card_printer_ip',''), ('selected_attendance_device_id',''), ('attendance_device_mode','push_adms'), ('attendance_device_ip',''), ('selected_receipt_printer_id',''), ('receipt_printer_mode','os_driver'), ('receipt_printer_connection','lan'), ('receipt_printer_ip',''), ('receipt_paper_width','80') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT IGNORE INTO services(code,barcode,category,name,sort_order,duration_minutes,price,discount_type,discount_value,bonus_amount,is_active) VALUES (NULL,NULL,'Barber Service','Peel of Mask',10,30,45000,'none',0,14000,1), ('SRV004',NULL,'Coloring Basic','Hair Coloring',20,90,150000,'none',0,38000,1), ('SRV003',NULL,'Coloring Highlight','Coloring Highlight',30,15,350000,'none',0,105000,1), (NULL,NULL,'Curly Perm','Curly Perm',40,30,350000,'none',0,105000,1), (NULL,NULL,'Down Perm','Down Perm',50,30,250000,'none',0,75000,1), ('SRV005',NULL,'Hair Coloring','Coloring Extend',60,20,350000,'none',0,105000,1), (NULL,NULL,'Hair Mask','Hair Mask',70,30,60000,'none',0,20000,1), ('SRV002',NULL,'Haircut','Haircut',80,45,90000,'none',0,25000,1), ('SRV001',NULL,'Massage','Massage',90,30,45000,'none',0,15000,1), (NULL,NULL,'Paket 1','Hair Cut + Massage',100,30,125000,'none',0,40000,1), (NULL,NULL,'Paket 2','Haircut Peel of Mask',110,30,125000,'none',0,40000,1), (NULL,NULL,'Paket 3','Hair Cut + Hair Mask',120,30,140000,'none',0,45000,1), (NULL,NULL,'Paket 4','Hair Cut + Massage + Hair Mask + Peel of Mask',130,30,220000,'none',0,74000,1); INSERT IGNORE INTO finance_categories(type,code,name,description,is_active,sort_order) VALUES ('income','INC-OTHER','Pemasukan Lainnya','Pemasukan non-transaksi jasa',1,10), ('income','INC-CAPITAL','Penambahan Modal','Setoran atau penambahan modal usaha',1,20), ('income','INC-ASSET','Penjualan Aset','Hasil penjualan aset/peralatan',1,30), ('expense','EXP-OPS','Operasional','Biaya operasional rutin',1,10), ('expense','EXP-SUPPLY','Bahan & Persediaan','Pembelian bahan, produk, dan persediaan',1,20), ('expense','EXP-SALARY','Gaji/Honor','Gaji, honor, dan upah di luar bonus otomatis',1,30), ('expense','EXP-UTILITY','Utilitas','Listrik, air, internet, dan utilitas lain',1,40), ('expense','EXP-MAINT','Perawatan','Perawatan peralatan dan fasilitas',1,50), ('expense','EXP-OTHER','Pengeluaran Lainnya','Pengeluaran di luar kategori utama',1,90); CREATE TABLE IF NOT EXISTS member_registrations ( id BIGINT AUTO_INCREMENT PRIMARY KEY, registration_code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, phone VARCHAR(40) NOT NULL, email VARCHAR(150) NULL, photo_path VARCHAR(255) NULL, address TEXT NULL, instagram VARCHAR(180) NULL, tiktok VARCHAR(180) NULL, facebook VARCHAR(180) NULL, social_media_other VARCHAR(255) NULL, custom_fields_json LONGTEXT NULL, gender ENUM('L','P','') NOT NULL DEFAULT '', birth_date DATE NULL, notes TEXT NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', source VARCHAR(50) NOT NULL DEFAULT 'public_link', submitted_ip VARCHAR(64) NULL, reviewed_by INT NULL, reviewed_at DATETIME NULL, review_notes TEXT NULL, approved_member_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(approved_member_id) REFERENCES members(id) ON DELETE SET NULL, INDEX idx_member_reg_status(status,created_at), INDEX idx_member_reg_phone(phone), INDEX idx_member_reg_email(email) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS member_approval_logs ( id BIGINT AUTO_INCREMENT PRIMARY KEY, registration_id BIGINT NOT NULL, action ENUM('approved','rejected') NOT NULL, reviewer_id INT NULL, review_notes TEXT NULL, member_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(registration_id) REFERENCES member_registrations(id) ON DELETE CASCADE, FOREIGN KEY(reviewer_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL, INDEX idx_member_approval_registration(registration_id,created_at) ) ENGINE=InnoDB; INSERT INTO settings(setting_key,setting_value) VALUES ('public_member_registration_enabled','1'), ('public_member_default_level','Regular'), ('public_member_default_discount','0'), ('public_member_terms','Dengan mengirim formulir ini, saya menyatakan data yang diberikan benar dan bersedia menjadi member.') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO settings(setting_key,setting_value) VALUES ('public_member_check_enabled','1'), ('member_activity_window_days','90') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); INSERT IGNORE INTO settings(setting_key,setting_value) VALUES ('upload_member_photo_max_mb','3'), ('upload_member_photo_extensions','jpg,jpeg,png,webp'), ('upload_user_avatar_max_mb','3'), ('upload_user_avatar_extensions','jpg,jpeg,png,webp'), ('upload_product_image_max_mb','4'), ('upload_product_image_extensions','jpg,jpeg,png,webp'), ('upload_attendance_photo_max_mb','4'), ('upload_attendance_photo_extensions','jpg,jpeg,png,webp'), ('upload_member_memory_max_mb','6'), ('upload_member_memory_extensions','jpg,jpeg,png,webp'), ('upload_branding_image_max_mb','6'), ('upload_branding_image_extensions','jpg,jpeg,png,webp'), ('upload_login_background_max_mb','6'), ('upload_login_background_extensions','jpg,jpeg,png,webp'), ('upload_qris_image_max_mb','6'), ('upload_qris_image_extensions','jpg,jpeg,png,webp'), ('upload_card_design_max_mb','10'), ('upload_card_design_extensions','jpg,jpeg,png,webp'), ('upload_payment_proof_max_mb','8'), ('upload_payment_proof_extensions','jpg,jpeg,png,webp,pdf'), ('upload_expense_evidence_max_mb','8'), ('upload_expense_evidence_extensions','jpg,jpeg,png,webp,pdf'), ('upload_renovation_evidence_max_mb','8'), ('upload_renovation_evidence_extensions','jpg,jpeg,png,webp,pdf'), ('upload_renovation_photo_max_mb','8'), ('upload_renovation_photo_extensions','jpg,jpeg,png,webp'), ('upload_driver_file_max_mb','200'), ('upload_driver_file_extensions','exe,msi,zip,dmg,pkg,deb,rpm'), ('upload_restore_file_max_mb','250'), ('upload_restore_file_extensions','sql,zip'), ('upload_member_import_max_mb','0'), ('upload_member_import_extensions','csv,xlsx'), ('upload_pre_member_import_max_mb','0'), ('upload_pre_member_import_extensions','xlsx'); CREATE TABLE IF NOT EXISTS catalog_import_batches( id BIGINT AUTO_INCREMENT PRIMARY KEY, import_type ENUM('services','products') NOT NULL, file_name VARCHAR(255) NOT NULL, duplicate_mode ENUM('skip','update') NOT NULL DEFAULT 'skip', total_rows INT NOT NULL DEFAULT 0, inserted_rows INT NOT NULL DEFAULT 0, updated_rows INT NOT NULL DEFAULT 0, skipped_rows INT NOT NULL DEFAULT 0, failed_rows INT NOT NULL DEFAULT 0, imported_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(imported_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_catalog_import_type_created(import_type,created_at), INDEX idx_catalog_import_user(imported_by,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS catalog_import_rows( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_id BIGINT NOT NULL, row_number INT NOT NULL, item_key VARCHAR(120) NULL, item_name VARCHAR(180) NULL, action_type ENUM('insert','update','skip','error') NOT NULL, status ENUM('success','warning','error') NOT NULL DEFAULT 'success', message VARCHAR(1000) NULL, reference_id BIGINT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(batch_id) REFERENCES catalog_import_batches(id) ON DELETE CASCADE, INDEX idx_catalog_import_rows_batch(batch_id,row_number), INDEX idx_catalog_import_rows_status(batch_id,status) ) ENGINE=InnoDB; INSERT IGNORE INTO settings(setting_key,setting_value) VALUES ('upload_service_import_max_mb','0'), ('upload_service_import_extensions','xlsx'), ('upload_product_import_max_mb','0'), ('upload_product_import_extensions','xlsx'); CREATE TABLE IF NOT EXISTS whatsapp_templates( id BIGINT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(150) NOT NULL, category VARCHAR(50) NOT NULL DEFAULT 'promo', message_body TEXT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by INT NULL, updated_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, FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_wa_template_active(is_active,category) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS whatsapp_broadcasts( id BIGINT AUTO_INCREMENT PRIMARY KEY, template_id BIGINT NULL, source_type ENUM('member','pre_member','non_member') NOT NULL, campaign_name VARCHAR(180) NOT NULL, message_snapshot TEXT NOT NULL, total_recipients INT NOT NULL DEFAULT 0, opened_count INT NOT NULL DEFAULT 0, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(template_id) REFERENCES whatsapp_templates(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_wa_broadcast_source_created(source_type,created_at), INDEX idx_wa_broadcast_creator(created_by,created_at) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS whatsapp_broadcast_recipients( id BIGINT AUTO_INCREMENT PRIMARY KEY, broadcast_id BIGINT NOT NULL, source_type ENUM('member','pre_member','non_member') NOT NULL, source_id BIGINT NOT NULL, recipient_name VARCHAR(180) NOT NULL, phone VARCHAR(50) NOT NULL, normalized_phone VARCHAR(50) NOT NULL, message_text TEXT NOT NULL, status ENUM('queued','opened','skipped') NOT NULL DEFAULT 'queued', opened_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(broadcast_id) REFERENCES whatsapp_broadcasts(id) ON DELETE CASCADE, UNIQUE KEY uq_wa_broadcast_source(broadcast_id,source_type,source_id), INDEX idx_wa_recipient_status(broadcast_id,status), INDEX idx_wa_recipient_phone(normalized_phone) ) ENGINE=InnoDB; INSERT IGNORE INTO whatsapp_templates(template_name,category,message_body,is_active,created_by) VALUES ('Promo Umum','promo','Halo {nama}, ada promo spesial dari {brand}. Dapatkan penawaran terbaik untuk layanan pilihan Anda. Silakan balas pesan ini untuk informasi dan reservasi.',1,NULL), ('Promo Member','promo','Halo {nama}, khusus Member {level} {brand}: nikmati promo spesial kami. Poin Anda saat ini {poin}. Tunjukkan kartu member saat berkunjung.',1,NULL); INSERT INTO settings(setting_key,setting_value) VALUES('attendance_photo_stamp_enabled','1') ON DUPLICATE KEY UPDATE setting_value=setting_value; INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.38.0') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); INSERT INTO settings(setting_key,setting_value) VALUES('member_card_download_engine','canvas_safe_assets_v3') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); INSERT IGNORE INTO settings(setting_key,setting_value) VALUES ('upload_user_import_max_mb','0'), ('upload_user_import_extensions','xlsx'); INSERT IGNORE INTO user_roles(user_id,role,assigned_by) SELECT id,role,NULL FROM users; 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',''); INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.38.0') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); -- v4.39.0 Mandatory Member Photo & Member Check Information INSERT INTO settings(setting_key,setting_value) VALUES ('member_photo_required','1'), ('member_check_info_enabled','1'), ('member_check_info_title','Informasi untuk Member'), ('member_check_info_body','Terima kasih telah menjadi bagian dari Prestige Barber Club. Informasi promo, layanan, atau pemberitahuan penting akan ditampilkan di area ini.'), ('schema_release','4.39.0') ON DUPLICATE KEY UPDATE setting_value=CASE WHEN setting_key IN ('member_photo_required','schema_release') THEN VALUES(setting_value) ELSE setting_value END; -- v4.40.4: Pra-Member membership validity / expiry INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.40.4') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); -- v4.40.8 release marker INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.41.0') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value); INSERT IGNORE INTO settings(setting_key,setting_value) VALUES ('pre_member_active_discount_type','fixed'), ('pre_member_active_discount_value','0'); -- v4.41.4: role/access hardening only; no table/column changes. -- v4.41.5 release marker: cashier may set membership payment PAID; no structural DB changes.