-- ============================================================================
-- FRESH DATABASE SETUP - Go.Mixue.One Dashboard
-- ============================================================================
-- Database: gradakan_gogens
-- Run this via cPanel phpMyAdmin → SQL tab
-- ============================================================================

-- Drop existing tables (WARNING: DELETES ALL DATA!)
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `shortlinks`;
DROP TABLE IF EXISTS `domains`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================================
-- Table: users
-- ============================================================================
CREATE TABLE `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `role` VARCHAR(50) NOT NULL DEFAULT 'user',
    `is_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,

    UNIQUE KEY `uk_username` (`username`),
    INDEX `idx_role` (`role`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: domains
-- ============================================================================
CREATE TABLE `domains` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `domain` VARCHAR(255) NOT NULL,
    `subdomain` VARCHAR(100) DEFAULT NULL,
    `document_root` VARCHAR(500) NOT NULL,
    `cloudflare_zone_id` VARCHAR(100) DEFAULT NULL,
    `cloudflare_record_id` VARCHAR(100) DEFAULT NULL,
    `nameservers` TEXT DEFAULT NULL COMMENT 'JSON array of nameservers',
    `status` ENUM('active', 'inactive', 'pending') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY `uk_domain` (`domain`),
    UNIQUE KEY `uk_subdomain` (`subdomain`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: shortlinks
-- ============================================================================
CREATE TABLE `shortlinks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `subdomain` VARCHAR(100) DEFAULT NULL,
    `domain_id` BIGINT UNSIGNED DEFAULT NULL,
    `use_global_domain` TINYINT(1) NOT NULL DEFAULT 0,
    `destination_url` VARCHAR(2048) NOT NULL,
    `og_title` VARCHAR(255) DEFAULT NULL,
    `og_description` TEXT DEFAULT NULL,
    `og_image` VARCHAR(2048) DEFAULT NULL,
    `og_type` VARCHAR(50) DEFAULT 'website',
    `fb_app_id` VARCHAR(100) DEFAULT NULL,
    `meta_keywords` VARCHAR(500) DEFAULT NULL,
    `meta_author` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('active', 'inactive', 'expired') NOT NULL DEFAULT 'active',
    `click_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `last_clicked_at` TIMESTAMP NULL DEFAULT NULL,
    `expires_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY `uk_slug` (`slug`),
    INDEX `idx_subdomain_slug` (`subdomain`, `slug`),
    INDEX `idx_domain_id` (`domain_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_clicks` (`click_count`),

    FOREIGN KEY `fk_shortlink_user` (`user_id`)
        REFERENCES `users`(`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    FOREIGN KEY `fk_shortlink_domain` (`domain_id`)
        REFERENCES `domains`(`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: activity_logs
-- ============================================================================
CREATE TABLE `activity_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50) DEFAULT NULL COMMENT 'domain, shortlink, user, etc',
    `entity_id` BIGINT UNSIGNED DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_entity` (`entity_type`, `entity_id`),
    INDEX `idx_created` (`created_at`),

    FOREIGN KEY `fk_log_user` (`user_id`)
        REFERENCES `users`(`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Table: settings
-- ============================================================================
CREATE TABLE `settings` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT DEFAULT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT 'string' COMMENT 'string, int, bool, json',
    `description` VARCHAR(500) DEFAULT NULL,
    `is_sensitive` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 for passwords/secrets',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY `uk_key` (`key`),
    INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- INSERT DEFAULT DATA
-- ============================================================================

-- Insert admin user
-- Username: admin
-- Password: password (CHANGE THIS IMMEDIATELY!)
INSERT INTO `users` (`username`, `password_hash`, `name`, `role`, `is_active`)
VALUES (
    'admin',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'Administrator',
    'admin',
    1
);

-- Insert default settings
INSERT INTO `settings` (`key`, `value`, `type`, `description`, `is_sensitive`) VALUES
('site_name', 'Go.Mixue.One', 'string', 'Application name', 0),
('default_redirect_delay', '3', 'int', 'Default shim redirect delay in seconds', 0),
('enable_analytics', '1', 'bool', 'Enable click tracking and analytics', 0),
('enable_og_tags', '1', 'bool', 'Enable Open Graph meta tags', 0),
('max_slug_length', '50', 'int', 'Maximum shortlink slug length', 0),
('allow_custom_slugs', '1', 'bool', 'Allow users to create custom slugs', 0),
('cpanel_host', '', 'string', 'cPanel hostname (without https://)', 1),
('cpanel_username', '', 'string', 'cPanel username', 1),
('cpanel_api_token', '', 'string', 'cPanel API token', 1),
('cpanel_port', '2083', 'int', 'cPanel port (2083 for SSL, 2082 for non-SSL)', 0),
('cloudflare_api_key', '', 'string', 'Cloudflare Global API Key', 1),
('cloudflare_email', '', 'string', 'Cloudflare account email', 1),
('server_ip', '', 'string', 'Server IP address for DNS records', 0);

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Check users
SELECT 'Users Created:' as info, COUNT(*) as count FROM users;
SELECT id, username, name, role, is_active FROM users;

-- Check settings
SELECT 'Settings Created:' as info, COUNT(*) as count FROM settings;
SELECT `key`, `value`, `type` FROM settings WHERE is_sensitive = 0;

-- Check tables
SELECT 'Tables Created:' as info;
SHOW TABLES;

-- ============================================================================
-- NEXT STEPS
-- ============================================================================
/*

1. ✅ Database structure created
2. ✅ Admin user created (username: admin, password: password)

IMPORTANT - CHANGE DEFAULT PASSWORD:

After first login, run this SQL to change password:

UPDATE users
SET password_hash = '$2y$10$YOUR_NEW_HASH_HERE'
WHERE username = 'admin';

To generate new hash, use PHP:
<?php echo password_hash('your_new_password', PASSWORD_BCRYPT); ?>

3. Configure system settings via Dashboard:
   - cPanel credentials
   - Cloudflare credentials (optional)
   - Server IP address

4. Test functionality:
   - Create domain via dashboard
   - Create shortlink
   - Test redirect: go.mixue.one/slug

*/