-- Royal Hotel Self-Hosted Payment Platform Schema
-- No third-party dependency. You are the primary authority.
-- Created: 2026-06-21

-- Payment Orders Table (internal order management)
CREATE TABLE IF NOT EXISTS payment_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(100) UNIQUE NOT NULL,
    transaction_token VARCHAR(100) NOT NULL,
    bill_id INT,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'INR',
    status ENUM('created', 'paid', 'failed', 'refunded', 'cancelled') DEFAULT 'created',
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    payment_method ENUM('upi', 'card', 'netbanking', 'wallet', 'cash', 'cheque', 'bank_transfer') DEFAULT 'cash',
    description TEXT,
    payment_id VARCHAR(100),
    transaction_id VARCHAR(100),
    paid_at TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bill_id) REFERENCES bills(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- API Keys Table (for external access to your platform)
CREATE TABLE IF NOT EXISTS api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(100) NOT NULL,
    api_key_hash VARCHAR(255) UNIQUE NOT NULL,
    api_secret_hash VARCHAR(255) NOT NULL,
    owner VARCHAR(100) NOT NULL,
    permissions ENUM('read', 'write', 'admin') DEFAULT 'read',
    status ENUM('active', 'revoked', 'expired') DEFAULT 'active',
    expiry_date DATE NOT NULL,
    last_used_at TIMESTAMP NULL,
    usage_count INT DEFAULT 0,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP NULL,
    revoked_by INT,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (revoked_by) REFERENCES users(id)
);

-- Payment Methods Configuration
CREATE TABLE IF NOT EXISTS payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default payment methods
INSERT INTO payment_methods (name, code, description, icon, is_active, sort_order) VALUES
('UPI', 'upi', 'Unified Payments Interface', 'fa-mobile-alt', TRUE, 1),
('Credit/Debit Card', 'card', 'Card payments', 'fa-credit-card', TRUE, 2),
('Net Banking', 'netbanking', 'Internet banking', 'fa-university', TRUE, 3),
('Wallet', 'wallet', 'Digital wallet', 'fa-wallet', TRUE, 4),
('Cash', 'cash', 'Cash payment', 'fa-money-bill-wave', TRUE, 5),
('Cheque', 'cheque', 'Cheque payment', 'fa-money-check', TRUE, 6),
('Bank Transfer', 'bank_transfer', 'Direct bank transfer', 'fa-exchange-alt', TRUE, 7);

-- Daily Payment Summary View
CREATE OR REPLACE VIEW daily_payment_summary AS
SELECT 
    DATE(created_at) as payment_date,
    COUNT(*) as total_transactions,
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) as total_successful,
    SUM(CASE WHEN status = 'failed' THEN amount ELSE 0 END) as total_failed,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) as total_refunded,
    COUNT(DISTINCT bill_id) as unique_bills_paid
FROM payment_orders
GROUP BY DATE(created_at)
ORDER BY payment_date DESC;