-- WEBALD AI - Database Schema
-- Import this file into phpMyAdmin (XAMPP) to create the database and tables.

CREATE DATABASE IF NOT EXISTS webald_ai CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE webald_ai;

-- ============================
-- Table: admins  (people who log into the admin panel)
-- ============================
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================
-- Table: users  (subscribers added from the admin panel — WordPress site owners)
-- ============================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    role ENUM('admin', 'subscriber') NOT NULL DEFAULT 'subscriber',
    status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
    site_url VARCHAR(255) DEFAULT NULL,      -- domain the license gets locked to
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================
-- Table: plans  (Pro, Basic, VIP, ...)
-- ============================
CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    billing_cycle ENUM('monthly', 'yearly') NOT NULL DEFAULT 'monthly',
    price_toman DECIMAL(12,0) NOT NULL DEFAULT 0,
    monthly_ai_request_limit INT NOT NULL DEFAULT 500, -- usage cap tied to plan
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================
-- Table: subscriptions  (a user assigned to a plan)
-- ============================
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    status ENUM('active', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB;

-- ============================
-- Table: licenses  (one license key per subscription)
-- ============================
CREATE TABLE licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscription_id INT NOT NULL,
    license_key VARCHAR(64) NOT NULL UNIQUE,
    activated_domain VARCHAR(255) DEFAULT NULL,  -- locked here on first activation
    status ENUM('active', 'suspended', 'revoked') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================
-- Table: usage_logs  (AI request tracking per license, used for quota + dashboard stats)
-- ============================
CREATE TABLE usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    request_type VARCHAR(100) DEFAULT 'general',
    tokens_used INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================
-- Table: ai_features  (individual AI capabilities the plugin can call)
-- ============================
CREATE TABLE ai_features (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    feature_key VARCHAR(100) NOT NULL UNIQUE,   -- machine key the plugin checks against
    description VARCHAR(255) DEFAULT NULL,
    system_prompt TEXT DEFAULT NULL,            -- appended to the AI's system prompt when a plan has this feature
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================
-- Table: plan_access  (which features are enabled on which plan)
-- ============================
CREATE TABLE plan_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_id INT NOT NULL,
    feature_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY plan_feature (plan_id, feature_id),
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id) REFERENCES ai_features(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================
-- Table: payments  (orders / payment records tied to a user's subscription)
-- ============================
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    subscription_id INT DEFAULT NULL,
    amount_toman DECIMAL(12,0) NOT NULL DEFAULT 0,
    method VARCHAR(50) NOT NULL DEFAULT 'manual',
    status ENUM('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================
-- Table: settings  (key-value store for general/limit settings)
-- ============================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'WEBALD AI'),
('support_email', ''),
('default_monthly_request_limit', '500'),
('max_requests_per_minute', '30'),
('license_grace_days', '3');

-- NOTE: no admin row is inserted here on purpose. Passwords must be hashed with PHP's
-- password_hash() at creation time — a hash typed/guessed by hand would not verify.
-- Run setup-admin.php once (see project root) to create your first admin account safely.

-- Sample plans (optional, delete if you don't want demo data)
INSERT INTO plans (name, billing_cycle, price_toman, monthly_ai_request_limit) VALUES
('پایه', 'monthly', 490000, 500),
('حرفه‌ای', 'monthly', 990000, 2000),
('ویژه', 'yearly', 9900000, 5000);