-- RiansGroup Center additive migration
-- NO DROP / DELETE / TRUNCATE. Existing data is preserved.
DELIMITER $$
CREATE PROCEDURE rg_add_col(IN p_table VARCHAR(64), IN p_col VARCHAR(64), IN p_sql TEXT)
BEGIN
 IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=p_table AND COLUMN_NAME=p_col) THEN
  SET @q=p_sql; PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
 END IF;
END$$
CREATE PROCEDURE rg_add_table(IN p_sql LONGTEXT)
BEGIN SET @q=p_sql; PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s; END$$
DELIMITER ;
CALL rg_add_col('users','last_login_at','ALTER TABLE users ADD COLUMN last_login_at DATETIME NULL');
CALL rg_add_col('users','verification_sent_at','ALTER TABLE users ADD COLUMN verification_sent_at DATETIME NULL');
CALL rg_add_col('membership_plans','max_invitations','ALTER TABLE membership_plans ADD COLUMN max_invitations INT NULL');
CALL rg_add_col('invitations','music_url','ALTER TABLE invitations ADD COLUMN music_url VARCHAR(255) NULL');
CALL rg_add_col('invitations','groom_photo','ALTER TABLE invitations ADD COLUMN groom_photo VARCHAR(255) NULL');
CALL rg_add_col('invitations','bride_photo','ALTER TABLE invitations ADD COLUMN bride_photo VARCHAR(255) NULL');
CALL rg_add_col('invitations','akad_date','ALTER TABLE invitations ADD COLUMN akad_date DATETIME NULL');
CALL rg_add_col('invitations','akad_location','ALTER TABLE invitations ADD COLUMN akad_location TEXT NULL');
CALL rg_add_col('invitations','akad_maps_url','ALTER TABLE invitations ADD COLUMN akad_maps_url VARCHAR(255) NULL');
CALL rg_add_col('invitations','resepsi_date','ALTER TABLE invitations ADD COLUMN resepsi_date DATETIME NULL');
CALL rg_add_col('invitations','resepsi_location','ALTER TABLE invitations ADD COLUMN resepsi_location TEXT NULL');
CALL rg_add_col('invitations','resepsi_maps_url','ALTER TABLE invitations ADD COLUMN resepsi_maps_url VARCHAR(255) NULL');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS smm_providers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,base_url VARCHAR(255) NOT NULL,api_key TEXT NOT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS smm_products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,provider_id BIGINT UNSIGNED NULL,service_id VARCHAR(80) NOT NULL,name VARCHAR(160) NOT NULL,category VARCHAR(100),price DECIMAL(14,2) NOT NULL DEFAULT 0,min_qty INT DEFAULT 1,max_qty INT DEFAULT 100000,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(provider_id),INDEX(service_id)) ENGINE=InnoDB');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS smm_orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NULL,provider_order_id VARCHAR(100),target VARCHAR(255) NOT NULL,quantity INT NOT NULL,price DECIMAL(14,2) NOT NULL DEFAULT 0,status VARCHAR(40) NOT NULL DEFAULT ''pending'',response LONGTEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP NULL,INDEX(user_id),INDEX(provider_order_id)) ENGINE=InnoDB');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS premium_products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,description TEXT,price DECIMAL(14,2) NOT NULL DEFAULT 0,stock INT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,image VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS premium_orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NULL,invoice_no VARCHAR(80) NOT NULL UNIQUE,total DECIMAL(14,2) NOT NULL DEFAULT 0,status VARCHAR(40) NOT NULL DEFAULT ''pending'',proof VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP NULL,INDEX(user_id)) ENGINE=InnoDB');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS premium_settings (id TINYINT UNSIGNED PRIMARY KEY,admin_whatsapp VARCHAR(30),notification_enabled TINYINT(1) NOT NULL DEFAULT 0,api_notes TEXT) ENGINE=InnoDB');
CALL rg_add_col('premium_settings','webhook_url','ALTER TABLE premium_settings ADD COLUMN webhook_url VARCHAR(255) NULL');
CALL rg_add_col('premium_settings','webhook_token','ALTER TABLE premium_settings ADD COLUMN webhook_token TEXT NULL');
CALL rg_add_table('CREATE TABLE IF NOT EXISTS panel_settings (id TINYINT UNSIGNED PRIMARY KEY,panel_key VARCHAR(50) UNIQUE NOT NULL,site_name VARCHAR(160) NOT NULL,site_slug VARCHAR(160) NOT NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB');
INSERT INTO panel_settings(id,panel_key,site_name,site_slug,enabled) VALUES(1,'smm','RiansGroup SMM','smm',1),(2,'premium','RiansGroup Premium Apps','premium-apps',1) ON DUPLICATE KEY UPDATE site_name=VALUES(site_name),site_slug=VALUES(site_slug);
INSERT INTO premium_settings(id,notification_enabled) VALUES(1,0) ON DUPLICATE KEY UPDATE id=id;
DROP PROCEDURE rg_add_col;
DROP PROCEDURE rg_add_table;