-- LOGoFiliate Kalender Kampanye — Database Schema
-- Import file ini via phpMyAdmin (cPanel > phpMyAdmin > Import)

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS weeks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS week_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  week_id INT NOT NULL,
  label VARCHAR(255) NOT NULL,
  color_code VARCHAR(10) NOT NULL, -- pj | dd | mp | pa
  FOREIGN KEY (week_id) REFERENCES weeks(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS holidays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  holiday_date DATE NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL
);

-- ============================================================
-- Seed data — dipindahin dari CAMPAIGN_WEEKS & HOLIDAYS di HTML lama
-- Aman dijalankan sekali. Kalau mau re-import, TRUNCATE dulu tabelnya.
-- ============================================================

INSERT INTO weeks (start_date, end_date) VALUES
('2026-07-13','2026-07-18'),
('2026-07-20','2026-07-25'),
('2026-07-27','2026-08-01'),
('2026-08-03','2026-08-08'),
('2026-08-10','2026-08-15'),
('2026-08-17','2026-08-22'),
('2026-08-24','2026-08-29');

INSERT INTO week_events (week_id, label, color_code) VALUES
(1,'Payday Juli · Prep','pj'), (1,'8.8 · Listing','dd'),
(2,'Payday Juli · Running','pj'), (2,'8.8 · Onboard','dd'), (2,'17 Agt · Listing','mp'),
(3,'Payday Juli · Running','pj'), (3,'8.8 · Approval','dd'), (3,'17 Agt · Listing','mp'),
(4,'8.8 · Running','dd'), (4,'17 Agt · Approval','mp'), (4,'Payday Agt · Listing','pa'),
(5,'8.8 · Running','dd'), (5,'17 Agt · Onboard','mp'), (5,'Payday Agt · Listing','pa'),
(6,'17 Agt · Running (Peak)','mp'), (6,'Payday Agt · Approval','pa'),
(7,'Payday Agt · Running (proyeksi)','pa');

INSERT INTO holidays (holiday_date, name) VALUES
('2026-08-17','Hari Kemerdekaan RI');