-- ============================================================ -- KOL Manager — MySQL Schema v3 (clean install) -- Drop all tables then recreate with latest structure -- ============================================================ SET NAMES utf8mb4; SET time_zone = '+07:00'; SET FOREIGN_KEY_CHECKS = 0; -- ── Drop all (children first) ───────────────────────────────── DROP TABLE IF EXISTS audit_log; DROP TABLE IF EXISTS password_resets; DROP TABLE IF EXISTS kpi_actuals; DROP TABLE IF EXISTS kpi_sets; DROP TABLE IF EXISTS kol_videos; DROP TABLE IF EXISTS kol_entry_items; DROP TABLE IF EXISTS kol_entries; DROP TABLE IF EXISTS video_entries; DROP TABLE IF EXISTS sales_orders; DROP TABLE IF EXISTS login_attempts; DROP TABLE IF EXISTS sessions; DROP TABLE IF EXISTS items; DROP TABLE IF EXISTS creator_categories; DROP TABLE IF EXISTS kol_creators; DROP TABLE IF EXISTS users; SET FOREIGN_KEY_CHECKS = 1; -- ── Users ───────────────────────────────────────────────────── CREATE TABLE users ( id CHAR(36) NOT NULL, un VARCHAR(50) NOT NULL UNIQUE, pw VARCHAR(255) NOT NULL, fn VARCHAR(100) DEFAULT '', ln VARCHAR(100) DEFAULT '', email VARCHAR(255) DEFAULT '', role VARCHAR(20) NOT NULL DEFAULT 'entrier', dep VARCHAR(100) DEFAULT '', photo MEDIUMTEXT DEFAULT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_role (role) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Sessions ────────────────────────────────────────────────── CREATE TABLE sessions ( token CHAR(64) NOT NULL, user_id CHAR(36) NOT NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (token), INDEX idx_user_id (user_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Login Attempts (rate limiting) ────────────────────────── CREATE TABLE login_attempts ( id INT NOT NULL AUTO_INCREMENT, ip VARCHAR(45) NOT NULL, un VARCHAR(50) DEFAULT '', succeeded TINYINT(1) NOT NULL DEFAULT 0, attempted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_ip (ip), INDEX idx_at (attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Items (product catalog) ─────────────────────────────────── CREATE TABLE items ( id CHAR(36) NOT NULL, nm VARCHAR(255) NOT NULL DEFAULT '', sku VARCHAR(100) NOT NULL UNIQUE, color VARCHAR(100) DEFAULT '', category VARCHAR(100) DEFAULT '', shop_link TEXT DEFAULT NULL, recom TINYINT(1) NOT NULL DEFAULT 0, created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_sku (sku) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Creator Categories ──────────────────────────────────────── CREATE TABLE creator_categories ( id CHAR(36) NOT NULL, name VARCHAR(100) NOT NULL, sort_order INT NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_sort (sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KOL Creators ────────────────────────────────────────────── CREATE TABLE kol_creators ( id CHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, tiktok VARCHAR(100) DEFAULT '', category VARCHAR(100) DEFAULT '', phone VARCHAR(50) DEFAULT '', email VARCHAR(255) DEFAULT '', address TEXT DEFAULT NULL, notes TEXT DEFAULT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_name (name), INDEX idx_tiktok (tiktok) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KOL Entries ─────────────────────────────────────────────── CREATE TABLE kol_entries ( id CHAR(36) NOT NULL, entry_date DATE DEFAULT NULL, specialist_id CHAR(36) DEFAULT NULL, reach_type VARCHAR(20) DEFAULT 'manual', creator_id CHAR(36) DEFAULT NULL, recv_name VARCHAR(255) DEFAULT '', phone VARCHAR(50) DEFAULT '', address TEXT DEFAULT NULL, courier VARCHAR(100) DEFAULT '', resi VARCHAR(255) DEFAULT '', status VARCHAR(50) NOT NULL DEFAULT 'To Be Shipped', uploaded_date DATE DEFAULT NULL, tbs_date DATE DEFAULT NULL, shipped_date DATE DEFAULT NULL, received_date DATE DEFAULT NULL, waiting_date DATE DEFAULT NULL, waiting_note TEXT DEFAULT NULL, created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_specialist_id (specialist_id), INDEX idx_creator (creator_id), INDEX idx_status (status), INDEX idx_entry_date (entry_date), FOREIGN KEY (specialist_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (creator_id) REFERENCES kol_creators(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KOL Entry Items ─────────────────────────────────────────── CREATE TABLE kol_entry_items ( id CHAR(36) NOT NULL, kol_entry_id CHAR(36) NOT NULL, product VARCHAR(100) DEFAULT '', product_label VARCHAR(255) DEFAULT '', qty INT NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_entry (kol_entry_id), FOREIGN KEY (kol_entry_id) REFERENCES kol_entries(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KOL Videos ──────────────────────────────────────────────── CREATE TABLE kol_videos ( id CHAR(36) NOT NULL, kol_entry_id CHAR(36) NOT NULL, content_id VARCHAR(100) DEFAULT NULL, real_content_id VARCHAR(100) DEFAULT NULL, link TEXT DEFAULT NULL, product VARCHAR(100) DEFAULT '', product_label VARCHAR(255) DEFAULT '', uploaded_date DATE DEFAULT NULL, tt_views INT NOT NULL DEFAULT 0, tt_likes INT NOT NULL DEFAULT 0, tt_comments INT NOT NULL DEFAULT 0, tt_shares INT NOT NULL DEFAULT 0, tt_downloads INT NOT NULL DEFAULT 0, tt_author_un VARCHAR(100) DEFAULT '', tt_author_nick VARCHAR(255) DEFAULT '', tt_upload_date DATE DEFAULT NULL, tt_cover_url TEXT DEFAULT NULL, cover_url TEXT DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE INDEX idx_content_id (content_id), INDEX idx_entry (kol_entry_id), FOREIGN KEY (kol_entry_id) REFERENCES kol_entries(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Sales Orders ────────────────────────────────────────────── CREATE TABLE sales_orders ( order_id VARCHAR(100) NOT NULL, product TEXT DEFAULT NULL, sku VARCHAR(255) DEFAULT '', seller_sku VARCHAR(255) DEFAULT '', price DECIMAL(15,2) NOT NULL DEFAULT 0, payment_amount DECIMAL(15,2) NOT NULL DEFAULT 0, currency VARCHAR(10) DEFAULT 'IDR', qty INT NOT NULL DEFAULT 1, pay_method VARCHAR(100) DEFAULT '', order_status VARCHAR(100) DEFAULT '', creator_un VARCHAR(100) DEFAULT '', content_type VARCHAR(100) DEFAULT '', content_id VARCHAR(100) DEFAULT '', comm_model VARCHAR(100) DEFAULT '', comm_pct DECIMAL(8,4) NOT NULL DEFAULT 0, est_ref_comm DECIMAL(15,2) NOT NULL DEFAULT 0, act_comm_pay DECIMAL(15,2) NOT NULL DEFAULT 0, shop_ad_comm_pct DECIMAL(8,4) NOT NULL DEFAULT 0, act_shop_ad_comm DECIMAL(15,2) NOT NULL DEFAULT 0, act_joint_bonus DECIMAL(15,2) NOT NULL DEFAULT 0, return_item INT NOT NULL DEFAULT 0, refund DECIMAL(15,2) NOT NULL DEFAULT 0, order_date DATE DEFAULT NULL, created_at_str VARCHAR(50) DEFAULT '', completed_at_str VARCHAR(50) DEFAULT '', platform VARCHAR(100) DEFAULT '', imported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, imported_by CHAR(36) DEFAULT NULL, PRIMARY KEY (order_id), INDEX idx_content_id (content_id), INDEX idx_order_status (order_status), INDEX idx_creator_un (creator_un), INDEX idx_order_date (order_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Video Entries (Internal Artist tracker) ─────────────────── CREATE TABLE video_entries ( id CHAR(36) NOT NULL, artist_id CHAR(36) DEFAULT NULL, content_id VARCHAR(100) DEFAULT '', real_content_id VARCHAR(100) DEFAULT NULL, link TEXT DEFAULT NULL, product VARCHAR(100) DEFAULT '', product_label VARCHAR(255) DEFAULT '', entry_date DATE DEFAULT NULL, upload_date DATE DEFAULT NULL, tt_views INT NOT NULL DEFAULT 0, tt_likes INT NOT NULL DEFAULT 0, tt_comments INT NOT NULL DEFAULT 0, tt_shares INT NOT NULL DEFAULT 0, tt_downloads INT NOT NULL DEFAULT 0, tt_author_un VARCHAR(100) DEFAULT '', tt_author_nick VARCHAR(255) DEFAULT '', tt_upload_date DATE DEFAULT NULL, tt_cover_url TEXT DEFAULT NULL, cover_url TEXT DEFAULT NULL, created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_artist_id (artist_id), INDEX idx_content_id (content_id), INDEX idx_entry_date (entry_date), FOREIGN KEY (artist_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KPI Sets ────────────────────────────────────────────────── CREATE TABLE kpi_sets ( id CHAR(36) NOT NULL, name VARCHAR(255) NOT NULL, kind VARCHAR(10) NOT NULL DEFAULT 'kol', from_date DATE DEFAULT NULL, to_date DATE DEFAULT NULL, metrics TEXT DEFAULT NULL, specialists TEXT DEFAULT NULL, created_by CHAR(36) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_kind (kind) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── KPI Actuals ─────────────────────────────────────────────── CREATE TABLE kpi_actuals ( id CHAR(36) NOT NULL, kpi_set_id CHAR(36) NOT NULL, specialist_id CHAR(36) DEFAULT NULL, views INT NOT NULL DEFAULT 0, likes INT NOT NULL DEFAULT 0, shares INT NOT NULL DEFAULT 0, comments INT NOT NULL DEFAULT 0, eng DECIMAL(8,4) NOT NULL DEFAULT 0, gmv DECIMAL(15,2) NOT NULL DEFAULT 0, orders INT NOT NULL DEFAULT 0, comm DECIMAL(15,2) NOT NULL DEFAULT 0, videos INT NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_actual (kpi_set_id, specialist_id), INDEX idx_kpi_set (kpi_set_id), FOREIGN KEY (kpi_set_id) REFERENCES kpi_sets(id) ON DELETE CASCADE, FOREIGN KEY (specialist_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Password Resets ────────────────────────────────────────── CREATE TABLE password_resets ( token CHAR(64) NOT NULL, user_id CHAR(36) NOT NULL, expires_at DATETIME NOT NULL, used TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (token), INDEX idx_user_id (user_id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Audit Log ───────────────────────────────────────────────── CREATE TABLE audit_log ( id INT NOT NULL AUTO_INCREMENT, user_id CHAR(36) DEFAULT NULL, user_un VARCHAR(50) DEFAULT NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(50) DEFAULT NULL, entity_id VARCHAR(36) DEFAULT NULL, detail TEXT DEFAULT NULL, ip VARCHAR(45) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_user_id (user_id), INDEX idx_action (action), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── Seed: superadmin ────────────────────────────────────────── -- Temporary password: Admin1234 -- Login with that, then change to your real password in Settings INSERT INTO users (id, un, pw, fn, role, active) VALUES ( UUID(), 'n.natasya', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Natasya', 'superadmin', 1 );