-- Combined Database Schema
-- Generated: 2026-03-18

-- Leads
CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Admin Users
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    role VARCHAR(20) DEFAULT 'admin',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Website Settings
CREATE TABLE IF NOT EXISTS website_settings (
    `key` VARCHAR(50) PRIMARY KEY,
    `value` TEXT,
    `label` VARCHAR(100),
    `type` VARCHAR(20) DEFAULT 'text',
    `group` VARCHAR(20) DEFAULT 'general'
);

-- Cases
CREATE TABLE IF NOT EXISTS cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    tag VARCHAR(100),
    category VARCHAR(100),
    img VARCHAR(255),
    description TEXT,
    content TEXT,
    challenge TEXT,
    solution TEXT,
    result_stability VARCHAR(100),
    result_throughput VARCHAR(100),
    result_cost VARCHAR(100),
    tech VARCHAR(255),
    is_featured BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    is_visible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    icon VARCHAR(100),
    content TEXT,
    sort_order INT DEFAULT 0,
    is_visible BOOLEAN DEFAULT TRUE
);

-- Tech Stack
CREATE TABLE IF NOT EXISTS tech_stack (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100),
    title VARCHAR(255) NOT NULL,
    icon VARCHAR(100),
    description TEXT,
    sort_order INT DEFAULT 0
);

-- Pricing
CREATE TABLE IF NOT EXISTS pricing_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price_range VARCHAR(100),
    features TEXT,
    is_featured BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0
);

-- Transformations
CREATE TABLE IF NOT EXISTS transformations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    results_data VARCHAR(255),
    description TEXT,
    img VARCHAR(255),
    video_url VARCHAR(255),
    sort_order INT DEFAULT 0
);

-- Default Data
INSERT IGNORE INTO admin_users (username, password, nickname) VALUES ('admin', '$2y$10$jHk/K4irYNwTB99K8hKSTeOcHEij/VFPzmPJq.X7DIoKXs5XzwhRS', '系统管理员');

INSERT IGNORE INTO website_settings (`key`, `value`, `label`, `type`, `group`) VALUES 
('site_name', '智域科技', '网站名称', 'text', 'general'),
('site_logo', 'assets/pasted-20260226-073317-a8105f30.png', '网站LOGO', 'image', 'general'),
('wechat_id', 'zhiyukj888', '微信号', 'text', 'contact'),
('wechat_qr', 'assets/images/qr.png', '微信二维码', 'image', 'contact'),
('tg_link', 'https://t.me/zhiyukj', 'Telegram链接', 'text', 'contact'),
('contact_email', 'contact@zhiyu.tech', '联系邮箱', 'text', 'contact'),
('contact_phone', '+86 138-0000-0000', '联系电话', 'text', 'contact'),
('company_address', '上海市浦东新区某某大厦', '公司地址', 'text', 'contact'),
('seo_title', '智域科技 - 全球领先的金融技术解决方案提供商', 'SEO标题', 'text', 'seo'),
('seo_keywords', '金融科技,交易所开发,云矿机,远控系统', 'SEO关键词', 'text', 'seo'),
('seo_description', '智域科技提供高端金融技术定制开发，涵盖交易所、资金盘、远控系统等。', 'SEO描述', 'textarea', 'seo');