-- Bank Statement Converter - Database Schema
-- MySQL/MariaDB

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    email_verification_token VARCHAR(255),
    email_verified_at TIMESTAMP NULL,
    credits_remaining INT DEFAULT 3,
    total_conversions INT DEFAULT 0,
    role ENUM('user', 'admin') DEFAULT 'user',
    is_active BOOLEAN DEFAULT TRUE,
    plan_id INT NULL,
    next_renewal_at TIMESTAMP NULL,
    two_factor_secret VARCHAR(255) NULL,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    pending_new_email VARCHAR(255) NULL,
    pending_email_token VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (email),
    INDEX (role),
    INDEX (email_verification_token)
);

CREATE TABLE billing_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    documents INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    features JSON,
    is_active BOOLEAN DEFAULT TRUE,
    is_public BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_credits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    documents_added INT NOT NULL,
    plan_id INT,
    amount_paid DECIMAL(10, 2),
    invoice_number VARCHAR(100),
    invoice_url VARCHAR(500),
    notes VARCHAR(500),
    added_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES billing_plans(id),
    FOREIGN KEY (added_by) REFERENCES users(id),
    INDEX (user_id),
    INDEX (created_at)
);

CREATE TABLE conversion_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    documents_used INT DEFAULT 1,
    bank_type VARCHAR(50),
    transaction_count INT,
    status ENUM('success', 'failed') DEFAULT 'success',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id),
    INDEX (created_at)
);

-- Email templates for customizable emails
CREATE TABLE email_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50) UNIQUE NOT NULL COMMENT 'verification, password_reset, welcome_user, etc',
    subject VARCHAR(255) NOT NULL,
    body_html LONGTEXT NOT NULL,
    body_text LONGTEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (type)
);

-- Password reset tokens
CREATE TABLE password_reset_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX (user_id),
    INDEX (expires_at),
    UNIQUE KEY unique_token (token_hash)
);

-- Insert default email templates
INSERT INTO email_templates (type, subject, body_html, body_text) VALUES
('verification', 'Verify your Bank Converter account', '<h2>Welcome to Bank Converter!</h2><p>Click the link below to verify your email address:</p><a href="{verification_link}">Verify Email</a><p>This link expires in 24 hours.</p>', 'Welcome to Bank Converter!\n\nClick the link below to verify your email address:\n{verification_link}\n\nThis link expires in 24 hours.'),
('password_reset', 'Reset your Bank Converter password', '<h2>Password Reset Request</h2><p>We received a request to reset your password. Click the link below to create a new password:</p><a href="{reset_link}">Reset Password</a><p>This link expires in 1 hour. If you didn''t request this, please ignore this email.</p>', 'Password Reset Request\n\nWe received a request to reset your password. Click the link below to create a new password:\n{reset_link}\n\nThis link expires in 1 hour. If you didn''t request this, please ignore this email.'),
('welcome_user', 'Welcome to Bank Converter!', '<h2>Welcome, {username}!</h2><p>Your account is ready to use. You have {credits} credits to get started.</p><p><a href="{app_url}/dashboard">Go to Dashboard</a></p>', 'Welcome, {username}!\n\nYour account is ready to use. You have {credits} credits to get started.\n\nVisit: {app_url}/dashboard');

-- Insert default billing plans
INSERT INTO billing_plans (name, documents, price, description, features) VALUES
('Free Trial', 5, 0.00, 'Perfect for testing', '[\"5 free conversions\", \"Basic support\"]'),
('Starter', 50, 4.99, 'Small business', '[\"50 documents/month\", \"Email support\", \"1 year history\"]'),
('Professional', 500, 29.99, 'Growing business', '[\"500 documents/month\", \"Priority support\", \"Unlimited history\"]'),
('Enterprise', 2000, 99.99, 'Large scale', '[\"2000 documents/month\", \"Dedicated support\", \"API access\"]');

-- Create admin user (password: "admin123" - change after first login!)
-- Hash generated: php -r "echo password_hash('admin123', PASSWORD_BCRYPT, ['cost' => 10]);"
-- Credits = -1 represents infinite conversions for admin
INSERT INTO users (username, email, password_hash, email_verified, credits_remaining, role) 
VALUES ('admin', 'admin@example.com', '$2y$10$eIZntDrQmkQp/eEqfbZ0peFGsOFO2RkrtV4hS04LqCLlAQEjf75Ja', TRUE, -1, 'admin');
