PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=10000;
PRAGMA temp_store=MEMORY;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    description TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    description TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS role_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_id INTEGER NOT NULL,
    permission_id INTEGER NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    UNIQUE(role_id, permission_id)
);

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    full_name TEXT DEFAULT NULL,
    bio TEXT DEFAULT NULL,
    avatar TEXT DEFAULT NULL,
    role_id INTEGER NOT NULL DEFAULT 2,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive','banned')),
    last_login DATETIME DEFAULT NULL,
    last_ip TEXT DEFAULT NULL,
    login_attempts INTEGER DEFAULT 0,
    locked_until DATETIME DEFAULT NULL,
    remember_token TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role_id);

CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    description TEXT DEFAULT NULL,
    meta_title TEXT DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    parent_id INTEGER DEFAULT NULL,
    sort_order INTEGER DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_categories_slug ON categories(slug);
CREATE INDEX IF NOT EXISTS idx_categories_status ON categories(status);
CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories(parent_id);

CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    slug TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_tags_slug ON tags(slug);

CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    excerpt TEXT DEFAULT NULL,
    content TEXT NOT NULL,
    thumbnail TEXT DEFAULT NULL,
    thumbnail_small TEXT DEFAULT NULL,
    thumbnail_medium TEXT DEFAULT NULL,
    thumbnail_large TEXT DEFAULT NULL,
    category_id INTEGER DEFAULT NULL,
    author_id INTEGER DEFAULT NULL,
    status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','published','scheduled')),
    visibility TEXT NOT NULL DEFAULT 'public' CHECK(visibility IN ('public','private','password')),
    password TEXT DEFAULT NULL,
    meta_title TEXT DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    focus_keyword TEXT DEFAULT NULL,
    canonical_url TEXT DEFAULT NULL,
    reading_time INTEGER DEFAULT 1,
    view_count INTEGER DEFAULT 0,
    is_breaking INTEGER DEFAULT 0,
    is_headline INTEGER DEFAULT 0,
    is_trending INTEGER DEFAULT 0,
    published_at DATETIME DEFAULT NULL,
    scheduled_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_articles_slug ON articles(slug);
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category_id);
CREATE INDEX IF NOT EXISTS idx_articles_author ON articles(author_id);
CREATE INDEX IF NOT EXISTS idx_articles_published ON articles(published_at);
CREATE INDEX IF NOT EXISTS idx_articles_created ON articles(created_at);
CREATE INDEX IF NOT EXISTS idx_articles_views ON articles(view_count);
CREATE INDEX IF NOT EXISTS idx_articles_status_published ON articles(status, published_at);

CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts USING fts5(title, excerpt, content, content=articles, content_rowid=id);

CREATE TRIGGER IF NOT EXISTS articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, excerpt, content) VALUES (new.id, new.title, new.excerpt, new.content);
END;
CREATE TRIGGER IF NOT EXISTS articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, excerpt, content) VALUES ('delete', old.id, old.title, old.excerpt, old.content);
END;
CREATE TRIGGER IF NOT EXISTS articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, excerpt, content) VALUES ('delete', old.id, old.title, old.excerpt, old.content);
    INSERT INTO articles_fts(rowid, title, excerpt, content) VALUES (new.id, new.title, new.excerpt, new.content);
END;

CREATE TABLE IF NOT EXISTS article_tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
    UNIQUE(article_id, tag_id)
);
CREATE INDEX IF NOT EXISTS idx_article_tags_article ON article_tags(article_id);
CREATE INDEX IF NOT EXISTS idx_article_tags_tag ON article_tags(tag_id);

CREATE TABLE IF NOT EXISTS media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    original_name TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    file_path TEXT NOT NULL,
    thumbnail_path TEXT DEFAULT NULL,
    alt_text TEXT DEFAULT NULL,
    caption TEXT DEFAULT NULL,
    width INTEGER DEFAULT NULL,
    height INTEGER DEFAULT NULL,
    uploaded_by INTEGER DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_media_uploaded_by ON media(uploaded_by);
CREATE INDEX IF NOT EXISTS idx_media_type ON media(mime_type);

CREATE TABLE IF NOT EXISTS ads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    image TEXT DEFAULT NULL,
    button_login_url TEXT DEFAULT NULL,
    button_claim_url TEXT DEFAULT NULL,
    button_register_url TEXT DEFAULT NULL,
    show_login INTEGER DEFAULT 1,
    show_claim INTEGER DEFAULT 1,
    show_register INTEGER DEFAULT 1,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
    start_date DATETIME DEFAULT NULL,
    end_date DATETIME DEFAULT NULL,
    max_impressions INTEGER DEFAULT 10000,
    impressions_count INTEGER DEFAULT 0,
    click_count INTEGER DEFAULT 0,
    running_text TEXT DEFAULT NULL,
    target_devices TEXT DEFAULT NULL,
    target_os TEXT DEFAULT NULL,
    target_browsers TEXT DEFAULT NULL,
    target_countries TEXT DEFAULT NULL,
    target_country_mode TEXT DEFAULT 'whitelist',
    target_mobile_brands TEXT DEFAULT NULL,
    block_bots INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ads_status ON ads(status);

CREATE TABLE IF NOT EXISTS ad_tracking (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ad_id INTEGER NOT NULL,
    action TEXT NOT NULL CHECK(action IN ('impression','click_login','click_claim','click_register')),
    ip_address TEXT DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_ad_tracking_ad ON ad_tracking(ad_id);
CREATE INDEX IF NOT EXISTS idx_ad_tracking_date ON ad_tracking(created_at);

CREATE TABLE IF NOT EXISTS pages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT NOT NULL,
    meta_title TEXT DEFAULT NULL,
    meta_description TEXT DEFAULT NULL,
    status TEXT NOT NULL DEFAULT 'published' CHECK(status IN ('draft','published')),
    sort_order INTEGER DEFAULT 0,
    show_in_menu INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug);
CREATE INDEX IF NOT EXISTS idx_pages_status ON pages(status);

CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT NOT NULL UNIQUE,
    setting_value TEXT DEFAULT NULL,
    setting_type TEXT DEFAULT 'text' CHECK(setting_type IN ('text','textarea','image','number','color','url','email','json')),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(setting_key);

CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    subject TEXT DEFAULT NULL,
    message TEXT NOT NULL,
    ip_address TEXT DEFAULT NULL,
    is_read INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS menus (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    parent_id INTEGER DEFAULT NULL,
    sort_order INTEGER DEFAULT 0,
    location TEXT DEFAULT 'header' CHECK(location IN ('header','footer','sidebar')),
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
    target TEXT DEFAULT '_self',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES menus(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_menus_location ON menus(location);
CREATE INDEX IF NOT EXISTS idx_menus_status ON menus(status);

CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER DEFAULT NULL,
    action TEXT NOT NULL,
    description TEXT DEFAULT NULL,
    ip_address TEXT DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_logs_user ON logs(user_id);
CREATE INDEX IF NOT EXISTS idx_logs_action ON logs(action);
CREATE INDEX IF NOT EXISTS idx_logs_date ON logs(created_at);

CREATE TABLE IF NOT EXISTS rate_limits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    limiter_key TEXT NOT NULL UNIQUE,
    attempts INTEGER NOT NULL DEFAULT 1,
    first_attempt INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_rate_limits_key ON rate_limits(limiter_key);

-- DEFAULT DATA
INSERT OR IGNORE INTO roles (id, name, slug, description) VALUES
(1, 'Super Admin', 'super_admin', 'Full access to all features'),
(2, 'Editor', 'editor', 'Can manage articles, categories, and tags'),
(3, 'Author', 'author', 'Can create and manage own articles');

INSERT OR IGNORE INTO permissions (id, name, slug) VALUES
(1, 'Manage Articles', 'manage_articles'),(2, 'Manage Categories', 'manage_categories'),
(3, 'Manage Tags', 'manage_tags'),(4, 'Manage Media', 'manage_media'),
(5, 'Manage Users', 'manage_users'),(6, 'Manage Ads', 'manage_ads'),
(7, 'Manage Pages', 'manage_pages'),(8, 'Manage Settings', 'manage_settings'),
(9, 'Manage Menus', 'manage_menus'),(10, 'View Logs', 'view_logs');

INSERT OR IGNORE INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions;
INSERT OR IGNORE INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE slug IN ('manage_articles','manage_categories','manage_tags','manage_media');
INSERT OR IGNORE INTO role_permissions (role_id, permission_id) SELECT 3, id FROM permissions WHERE slug = 'manage_articles';

INSERT OR IGNORE INTO users (id, username, email, password, full_name, role_id, status) VALUES (1, 'admin', 'admin@example.com', '$2y$12$Z9HApVC4kH3gSUvioQf87OEPUiQHUC32L1Nr74c9i91wb2ESIMw62', 'Super Admin', 1, 'active');

INSERT OR IGNORE INTO categories (id, name, slug, description) VALUES
(7, 'Gaya Hidup Digital', 'gaya-hidup-digital', 'Tips dan gaya hidup di era digital'),
(8, 'Kesehatan Mental', 'kesehatan-mental', 'Edukasi kesehatan mental'),
(9, 'Edukasi Waspada Taruhan', 'edukasi-waspada-taruhan', 'Kampanye waspada taruhan online'),
(10, 'Edukasi & Kesehatan Mental', 'edukasi-kesehatan-mental', 'Edukasi dan kesehatan mental'),
(16, 'Olahraga', 'olahraga', 'Berita olahraga');

INSERT OR IGNORE INTO settings (setting_key, setting_value, setting_type) VALUES
('site_title','NewsHub - Portal Berita Terpercaya','text'),
('site_description','Portal berita terkini dengan informasi akurat, terpercaya, dan terupdate setiap hari.','textarea'),
('site_keywords','berita, news, berita terkini, indonesia','text'),
('site_logo','','image'),('site_favicon','','image'),('site_url','https://newshub.test','url'),
('site_email','info@newshub.test','email'),('site_phone','','text'),('site_address','','textarea'),
('google_analytics','','text'),('google_search_console','','text'),
('bing_verification','','text'),('yandex_verification','','text'),
('facebook_url','','url'),('twitter_url','','url'),('instagram_url','','url'),
('youtube_url','','url'),('tiktok_url','','url'),('posts_per_page','12','number'),
('enable_cache','1','number'),('cache_duration','3600','number'),
('enable_dark_mode','1','number'),('footer_text','© 2026 NewsHub. All rights reserved.','text'),
('maintenance_mode','0','number'),
('telegram_bot_token','','text'),
('telegram_chat_id','','text');

INSERT OR IGNORE INTO pages (id, title, slug, content, status, show_in_menu) VALUES
(1, 'About Us', 'about', '<h2>Tentang Kami</h2><p>NewsHub adalah portal berita terpercaya yang menyajikan informasi terkini dan akurat.</p>', 'published', 1),
(2, 'Contact Us', 'contact', '<h2>Hubungi Kami</h2><p>Silakan hubungi kami melalui form di bawah ini.</p>', 'published', 1),
(3, 'Privacy Policy', 'privacy-policy', '<h2>Kebijakan Privasi</h2><p>Kebijakan privasi menjelaskan bagaimana kami mengumpulkan dan menggunakan data Anda.</p>', 'published', 1),
(4, 'Disclaimer', 'disclaimer', '<h2>Disclaimer</h2><p>Semua informasi bersifat informatif.</p>', 'published', 1),
(5, 'Terms and Conditions', 'terms-and-conditions', '<h2>Syarat dan Ketentuan</h2><p>Dengan mengakses website ini, Anda setuju dengan syarat dan ketentuan.</p>', 'published', 1),
(6, 'Pedoman Media Siber', 'pedoman-media-siber', '<h2>Pedoman Media Siber</h2><p>Kami mematuhi pedoman media siber yang berlaku di Indonesia.</p>', 'published', 1),
(7, 'Redaksi', 'redaksi', '<h2>Redaksi</h2><p>Daftar tim redaksi NewsHub.</p>', 'published', 1);

INSERT OR IGNORE INTO menus (id, name, url, parent_id, sort_order, location, status) VALUES
(1, 'Home', '/', NULL, 1, 'header', 'active'),
(5, 'Olahraga', '/category/olahraga', NULL, 2, 'header', 'active'),
(7, 'Privacy Policy', '/page/kebijakan-privasi', NULL, 1, 'footer', 'active'),
(8, 'Disclaimer', '/page/disclaimer', NULL, 2, 'footer', 'active'),
(9, 'Syarat & Ketentuan', '/page/syarat-ketentuan', NULL, 3, 'footer', 'active'),
(10, 'Pedoman Pemberitaan', '/page/pedoman-pemberitaan', NULL, 4, 'footer', 'active'),
(11, 'Struktur Organisasi', '/page/struktur-organisasi', NULL, 5, 'footer', 'active'),
(12, 'Kontak', '/page/contact', NULL, 6, 'footer', 'active');