-- =====================================================================
-- KEY STORE PLATFORM - COMPLETE MYSQL SCHEMA
-- Admin + Reseller + User | Manual Key Inventory System
-- Engine: InnoDB | Charset: utf8mb4
-- =====================================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- USERS
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password VARCHAR(255) NULL,                -- NULL allowed for Google-only accounts
    google_id VARCHAR(190) NULL,
    photo_url VARCHAR(500) NULL,
    role ENUM('admin','reseller','user') NOT NULL DEFAULT 'user',
    status ENUM('active','suspended','banned') NOT NULL DEFAULT 'active',
    balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reset_token VARCHAR(191) NULL,
    reset_token_expires DATETIME NULL,
    reseller_request_status ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login DATETIME NULL,
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_google_id (google_id),
    KEY idx_users_role (role),
    KEY idx_users_status (status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- PRODUCTS
-- ---------------------------------------------------------------------
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(170) NOT NULL,
    description TEXT NULL,
    banner_url VARCHAR(500) NULL,
    icon_url VARCHAR(500) NULL,
    version VARCHAR(50) NULL,
    features TEXT NULL,
    update_info TEXT NULL,
    support_url VARCHAR(500) NULL,
    video_url VARCHAR(500) NULL,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_products_slug (slug),
    KEY idx_products_status (status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- PLANS
-- ---------------------------------------------------------------------
CREATE TABLE plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    duration_value INT UNSIGNED NOT NULL,
    duration_unit ENUM('minutes','hours','days') NOT NULL,
    user_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reseller_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_plans_product (product_id),
    KEY idx_plans_status (status),
    CONSTRAINT fk_plans_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- MANUAL KEYS (the core inventory table - NEVER auto-populated by purchase logic)
-- ---------------------------------------------------------------------
CREATE TABLE `keys` (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    key_code VARCHAR(255) NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,
    status ENUM('available','reserved','sold','active','expired','banned','revoked') NOT NULL DEFAULT 'available',
    owner_user_id BIGINT UNSIGNED NULL,
    reseller_id BIGINT UNSIGNED NULL,
    order_id BIGINT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    sold_at DATETIME NULL,
    activated_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_keys_code (key_code),
    KEY idx_keys_product_plan_status (product_id, plan_id, status),
    KEY idx_keys_owner (owner_user_id),
    KEY idx_keys_reseller (reseller_id),
    KEY idx_keys_order (order_id),
    CONSTRAINT fk_keys_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    CONSTRAINT fk_keys_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
    CONSTRAINT fk_keys_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_keys_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- ORDERS
-- ---------------------------------------------------------------------
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(40) NOT NULL,
    buyer_id BIGINT UNSIGNED NOT NULL,
    buyer_role ENUM('user','reseller') NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    payment_method VARCHAR(50) NOT NULL DEFAULT 'wallet',
    payment_status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'paid',
    order_status ENUM('completed','cancelled') NOT NULL DEFAULT 'completed',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_orders_number (order_number),
    KEY idx_orders_buyer (buyer_id),
    KEY idx_orders_product_plan (product_id, plan_id),
    CONSTRAINT fk_orders_buyer FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    CONSTRAINT fk_orders_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- ORDER ITEMS
-- ---------------------------------------------------------------------
CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    key_id BIGINT UNSIGNED NOT NULL,
    price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_order_items_order (order_id),
    KEY idx_order_items_key (key_id),
    CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_key FOREIGN KEY (key_id) REFERENCES `keys`(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- TRANSACTIONS
-- ---------------------------------------------------------------------
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('wallet_credit','wallet_debit','key_purchase','reseller_purchase','reseller_sale','admin_adjustment','refund') NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    balance_before DECIMAL(14,2) NOT NULL,
    balance_after DECIMAL(14,2) NOT NULL,
    reference_id VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    status ENUM('success','failed','pending') NOT NULL DEFAULT 'success',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_transactions_user (user_id),
    KEY idx_transactions_type (type),
    CONSTRAINT fk_transactions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- PAYMENT REQUESTS
-- ---------------------------------------------------------------------
CREATE TABLE payment_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    transaction_reference VARCHAR(150) NULL,
    screenshot_url VARCHAR(500) NULL,
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_payment_requests_user (user_id),
    KEY idx_payment_requests_status (status),
    CONSTRAINT fk_payment_requests_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- RESELLER CUSTOMERS
-- ---------------------------------------------------------------------
CREATE TABLE reseller_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_reseller_customer (reseller_id, customer_id),
    CONSTRAINT fk_rc_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rc_customer FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- RESELLER SALES
-- ---------------------------------------------------------------------
CREATE TABLE reseller_sales (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reseller_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    key_id BIGINT UNSIGNED NOT NULL,
    wholesale_price DECIMAL(14,2) NOT NULL,
    selling_price DECIMAL(14,2) NOT NULL,
    profit DECIMAL(14,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_reseller_sales_reseller (reseller_id),
    KEY idx_reseller_sales_customer (customer_id),
    CONSTRAINT fk_rs_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rs_customer FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_rs_key FOREIGN KEY (key_id) REFERENCES `keys`(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- NOTIFICATIONS
-- ---------------------------------------------------------------------
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    image_url VARCHAR(500) NULL,
    target_role ENUM('all','users','resellers','individual') NOT NULL DEFAULT 'all',
    target_user_id BIGINT UNSIGNED NULL,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notifications_target (target_role, target_user_id),
    CONSTRAINT fk_notifications_user FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- NOTIFICATION READS (tracks per-user read state for badge counts)
-- ---------------------------------------------------------------------
CREATE TABLE notification_reads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    notification_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_notif_read (notification_id, user_id),
    CONSTRAINT fk_nr_notification FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
    CONSTRAINT fk_nr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- SETTINGS (key-value store for site config)
-- ---------------------------------------------------------------------
CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_settings_key (setting_key)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- DEFAULT SETTINGS
-- ---------------------------------------------------------------------
INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'NEON KEY STORE'),
('logo_url', ''),
('favicon_url', ''),
('banner_url', ''),
('announcement', ''),
('telegram_url', ''),
('support_url', ''),
('video_url', ''),
('footer_text', '© 2026 Neon Key Store. All rights reserved.'),
('currency', 'INR'),
('currency_symbol', '₹'),
('maintenance_mode', '0'),
('maintenance_title', 'Under Maintenance'),
('maintenance_message', 'We are performing scheduled maintenance. Please check back soon.'),
('registration_enabled', '1'),
('google_login_enabled', '1'),
('google_client_id', ''),
('reseller_registration_enabled', '1'),
('payment_upi_id', ''),
('payment_qr_url', ''),
('payment_instructions', 'Pay to the UPI ID above and submit your transaction reference.'),
('payment_min_amount', '10'),
('payment_max_amount', '50000'),
('payment_method_name', 'UPI'),
('payment_support_contact', ''),
('stock_visible_to_users', '1');

-- ---------------------------------------------------------------------
-- SAMPLE PRODUCT + PLAN (optional, remove if not needed)
-- ---------------------------------------------------------------------
-- INSERT INTO products (name, slug, description, status) VALUES ('Sample Mod', 'sample-mod', 'Sample product description', 'active');
-- INSERT INTO plans (product_id, name, duration_value, duration_unit, user_price, reseller_price, status) VALUES (1, '1 Day', 1, 'days', 49.00, 30.00, 'active');

-- =====================================================================
-- END OF SCHEMA
-- =====================================================================