CREATE DATABASE IF NOT EXISTS undangin CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE undangin;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, phone VARCHAR(30), password VARCHAR(255) NOT NULL, google_id VARCHAR(100) NULL UNIQUE, verification_token VARCHAR(100) NULL, verified_at DATETIME NULL, role ENUM('customer','admin') NOT NULL DEFAULT 'customer', status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL, slug VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NULL, name VARCHAR(150) NOT NULL, slug VARCHAR(170) NOT NULL UNIQUE, membership_plan_id BIGINT UNSIGNED NULL, theme_slug VARCHAR(100) NULL, description TEXT, price DECIMAL(12,2) NOT NULL, sale_price DECIMAL(12,2) NULL, thumbnail VARCHAR(255), demo_url VARCHAR(255), is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL);
CREATE TABLE coupons (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, type ENUM('percent','fixed') NOT NULL, value DECIMAL(12,2) NOT NULL, min_purchase DECIMAL(12,2) DEFAULT 0, max_uses INT NULL, used_count INT NOT NULL DEFAULT 0, starts_at DATETIME NULL, ends_at DATETIME NULL, is_active TINYINT(1) DEFAULT 1);
CREATE TABLE orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_no VARCHAR(35) NOT NULL UNIQUE, user_id BIGINT UNSIGNED NOT NULL, subtotal DECIMAL(12,2) NOT NULL, discount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL, coupon_code VARCHAR(50), status ENUM('pending','waiting_payment','paid','processing','completed','cancelled','expired') NOT NULL DEFAULT 'waiting_payment', payment_method VARCHAR(50), payment_proof VARCHAR(255), paid_at DATETIME NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, product_name VARCHAR(150) NOT NULL, price DECIMAL(12,2) NOT NULL, FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES products(id));
CREATE TABLE invitations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_item_id BIGINT UNSIGNED NOT NULL UNIQUE, user_id BIGINT UNSIGNED NOT NULL, slug VARCHAR(120) NOT NULL UNIQUE, theme_slug VARCHAR(100) NOT NULL DEFAULT 'blue-elegant', groom_name VARCHAR(120), bride_name VARCHAR(120), event_date DATETIME NULL, event_location TEXT, maps_url VARCHAR(255), akad_date DATETIME NULL, akad_location TEXT NULL, akad_maps_url VARCHAR(255) NULL, resepsi_date DATETIME NULL, resepsi_location TEXT NULL, resepsi_maps_url VARCHAR(255) NULL, cover_image VARCHAR(255), groom_photo VARCHAR(255) NULL, bride_photo VARCHAR(255) NULL, story TEXT, music VARCHAR(255) NULL, is_published TINYINT(1) DEFAULT 0, expires_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE invitation_galleries (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id BIGINT UNSIGNED NOT NULL, image VARCHAR(255) NOT NULL, caption VARCHAR(150), sort_order INT DEFAULT 0, FOREIGN KEY(invitation_id) REFERENCES invitations(id) ON DELETE CASCADE);
CREATE TABLE invitation_accounts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id BIGINT UNSIGNED NOT NULL, bank_name VARCHAR(100) NOT NULL, account_name VARCHAR(120) NOT NULL, account_number VARCHAR(80) NOT NULL, FOREIGN KEY(invitation_id) REFERENCES invitations(id) ON DELETE CASCADE);
CREATE TABLE guests (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, attendance ENUM('hadir','tidak_hadir','ragu') DEFAULT 'ragu', message TEXT, is_hidden TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(invitation_id) REFERENCES invitations(id) ON DELETE CASCADE);
CREATE TABLE banners (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, subtitle VARCHAR(255), image VARCHAR(255), link_url VARCHAR(255), is_active TINYINT(1) DEFAULT 1, sort_order INT DEFAULT 0);
CREATE TABLE pages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, slug VARCHAR(160) NOT NULL UNIQUE, body LONGTEXT, is_published TINYINT(1) DEFAULT 1);
CREATE TABLE settings (setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT);
INSERT INTO categories(name,slug) VALUES ('Pernikahan','pernikahan'),('Ulang Tahun','ulang-tahun'),('Aqiqah','aqiqah');
INSERT INTO settings VALUES ('site_name','RiansGroup Invitation'),('brand_powered','Powered by RiansGroup Digital Evolusi'),('site_tagline','Undangan digital yang berkesan'),('bank_name','BCA'),('bank_account','1234567890'),('bank_holder','Undangin Indonesia');
INSERT INTO users(username,name,email,password,role) VALUES ('admin','Administrator','admin@undangin.test','$2y$12$epXWzsVHMoXp8ylmP3B2geQXhQjiMk/h3R.PUFH90Csr8hEALzLqa','admin');
INSERT INTO products(category_id,name,slug,description,price,sale_price,is_active) VALUES (1,'Floral Elegance','floral-elegance','Tema pernikahan bernuansa floral yang elegan dan responsif.',150000,125000,1);
-- Akun pengembangan: admin@undangin.test / Admin123! . Ganti kata sandi ini sebelum situs dipublikasikan.


CREATE TABLE membership_plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL, slug VARCHAR(140) NOT NULL UNIQUE, description TEXT,
 duration_days INT NULL, max_invitations INT NULL, price DECIMAL(12,2) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_memberships (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL,
 order_id BIGINT UNSIGNED NULL, starts_at DATETIME NOT NULL, ends_at DATETIME NULL,
 status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_membership_order(order_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(plan_id) REFERENCES membership_plans(id) ON DELETE RESTRICT, FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE SET NULL
);
CREATE TABLE themes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, slug VARCHAR(150) NOT NULL UNIQUE,
 description TEXT, thumbnail VARCHAR(255), folder VARCHAR(150) NOT NULL, version VARCHAR(30) NOT NULL DEFAULT '1.0.0',
 is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE guest_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id BIGINT UNSIGNED NOT NULL, guest_name VARCHAR(150) NOT NULL,
 phone VARCHAR(30), token VARCHAR(64) NOT NULL UNIQUE, visit_count INT NOT NULL DEFAULT 0, is_hidden TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(invitation_id) REFERENCES invitations(id) ON DELETE CASCADE
);
CREATE TABLE whatsapp_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, message TEXT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public_invitations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, manage_token VARCHAR(64) NOT NULL UNIQUE, slug VARCHAR(120) NOT NULL UNIQUE,
 theme VARCHAR(100) NOT NULL DEFAULT 'blue-elegant', groom_name VARCHAR(120), bride_name VARCHAR(120), event_date DATETIME NULL,
 venue VARCHAR(255), address TEXT, maps_url VARCHAR(255), love_story TEXT, cover_note TEXT, bank_name VARCHAR(100),
 bank_account VARCHAR(100), bank_holder VARCHAR(120), is_published TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE public_invitation_rsvps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id BIGINT UNSIGNED NOT NULL, guest_name VARCHAR(150) NOT NULL,
 attendance ENUM('hadir','tidak_hadir','ragu') DEFAULT 'ragu', message TEXT, is_hidden TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(invitation_id) REFERENCES public_invitations(id) ON DELETE CASCADE
);

INSERT INTO membership_plans(name,slug,description,duration_days,max_invitations,price,is_active) VALUES
('Basic 30 Hari','basic-30','Undangan aktif selama 30 hari.',30,1,0,1),
('Premium 365 Hari','premium-365','Undangan aktif selama 365 hari.',365,5,0,1),
('Lifetime','lifetime','Undangan tanpa masa kedaluwarsa.',NULL,NULL,0,1);
INSERT INTO themes(name,slug,description,folder,version,is_active) VALUES
('Blue Elegant','blue-elegant','Tema biru modern elegan.','blue-elegant','1.0.0',1),
('Royal Blue','royal-blue','Tema biru royal premium.','royal-blue','1.0.0',1),
('Midnight Islamic','midnight-islamic','Tema biru malam bernuansa islami.','midnight-islamic','1.0.0',1);
INSERT INTO whatsapp_templates(name,message,is_active) VALUES
('Undangan Pernikahan','Assalamu’alaikum {nama_tamu}, kami mengundang Anda untuk hadir di acara {mempelai}. Detail undangan: {link}. Terima kasih atas doa dan kehadirannya.',1),
('Undangan Singkat','Kepada {nama_tamu}, Anda diundang ke acara {mempelai}. Silakan buka undangan: {link}.',1);

-- Branding / dashboard defaults
INSERT INTO settings(setting_key,setting_value) VALUES
('site_name','RiansGroup Invitation'),
('brand_powered','Powered by RiansGroup Digital Evolusi'),
('prayer_city','Banjarmasin'),
('prayer_country','Indonesia'),
('prayer_method','20')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);
-- Optional compatibility columns for media-rich invitations (run only if upgrading an older database)
-- ALTER TABLE invitations ADD COLUMN groom_photo VARCHAR(255) NULL AFTER cover_image;
-- ALTER TABLE invitations ADD COLUMN bride_photo VARCHAR(255) NULL AFTER groom_photo;
-- ALTER TABLE invitations ADD COLUMN music VARCHAR(255) NULL AFTER story;