-- Database schema for Recharge API Mediator

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS commission_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    company_name VARCHAR(100),
    wallet_balance DECIMAL(15, 4) DEFAULT 0.0000,
    api_token VARCHAR(64) UNIQUE NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    package_id INT NULL,
    logo VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (package_id) REFERENCES commission_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    getting_commission DECIMAL(5, 2) DEFAULT 0.00,
    selling_commission DECIMAL(5, 2) DEFAULT 0.00,
    company_id INT DEFAULT 1,
    status ENUM('active', 'inactive') DEFAULT 'active',
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NULL,
    api_url VARCHAR(255) NOT NULL DEFAULT 'https://mrobotics.in/api/v1/recharge',
    api_token VARCHAR(255) NOT NULL,
    api_username VARCHAR(100) NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    UNIQUE KEY (vendor_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    product_id INT NOT NULL,
    recharge_number VARCHAR(20) NOT NULL,
    requested_amount DECIMAL(10, 2) NOT NULL,
    deducted_amount DECIMAL(10, 4) NOT NULL,
    selling_commission_percentage DECIMAL(5, 2) NOT NULL,
    commission_paid_to_vendor DECIMAL(10, 4) NOT NULL,
    getting_commission_percentage DECIMAL(5, 2) NOT NULL,
    admin_commission_earned DECIMAL(10, 4) NOT NULL,
    client_txid VARCHAR(100) NULL,
    system_txid VARCHAR(64) UNIQUE NOT NULL,
    upstream_txid VARCHAR(100) NULL,
    status ENUM('Success', 'Pending', 'Failed', 'Refunded') DEFAULT 'Pending',
    response_json TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT NOT NULL,
    amount DECIMAL(15, 4) NOT NULL,
    balance_before DECIMAL(15, 4) NOT NULL,
    balance_after DECIMAL(15, 4) NOT NULL,
    type ENUM('Credit', 'Debit', 'Refund') NOT NULL,
    description VARCHAR(255) NOT NULL,
    transaction_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NULL,
    log_type ENUM('downstream', 'upstream') NOT NULL,
    api_endpoint VARCHAR(255) NOT NULL,
    request_headers TEXT NULL,
    request_payload TEXT NULL,
    response_payload TEXT NULL,
    response_code INT DEFAULT 200,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS package_commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    package_id INT NOT NULL,
    product_id INT NOT NULL,
    selling_commission DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
    status ENUM('active', 'inactive') DEFAULT 'active',
    FOREIGN KEY (package_id) REFERENCES commission_packages(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY (package_id, product_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS global_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS owners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS software_billing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    billing_month VARCHAR(7) UNIQUE NOT NULL,
    total_recharge_amount DECIMAL(15, 4) DEFAULT 0.0000,
    calculated_charge DECIMAL(15, 4) DEFAULT 0.0000,
    status ENUM('Paid', 'Unpaid') DEFAULT 'Unpaid',
    due_date DATE NOT NULL,
    extended_date DATE DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Seed default Owner user (owner / owner123)
INSERT IGNORE INTO owners (id, username, password_hash) VALUES (1, 'owner', '$2y$10$zQuDxlNRpuAHm8nuBr6CKunPk1ebCrDqg9Jwde6ftyOSc03gvqNuC');

-- Seed default billing settings
INSERT IGNORE INTO global_settings (setting_key, setting_value) VALUES 
    ('owner_min_monthly_charge', '500'),
    ('owner_charge_per_lakh', '150'),
    ('owner_due_day_of_month', '10');