-- Rockbridge Capital Portal Database Schema
CREATE DATABASE IF NOT EXISTS rbportal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rbportal;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(50),
    is_admin TINYINT(1) DEFAULT 0,
    must_change_password TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL,
    notes TEXT NULL
);

CREATE TABLE IF NOT EXISTS service_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    iban_prefix VARCHAR(10),
    country VARCHAR(100),
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS investments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    service_type_id INT NOT NULL,
    bank_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'EUR',
    investment_date DATE NOT NULL,
    maturity_date DATE NULL,
    status ENUM('aktiv','abgeschlossen','gekuendigt') DEFAULT 'aktiv',
    notes TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_by INT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (service_type_id) REFERENCES service_types(id),
    FOREIGN KEY (bank_id) REFERENCES banks(id)
);

CREATE TABLE IF NOT EXISTS fdrs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    investment_id INT NOT NULL,
    fdr_number VARCHAR(100) NOT NULL UNIQUE,
    document_type VARCHAR(100) DEFAULT 'Festgeldbestaetigung',
    issue_date DATE NOT NULL,
    valid_until DATE NULL,
    description TEXT NULL,
    status ENUM('aktiv','abgelaufen','storniert') DEFAULT 'aktiv',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (investment_id) REFERENCES investments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(200) NOT NULL,
    details TEXT NULL,
    ip_address VARCHAR(45),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO service_types (name, description) VALUES
('Festgeld', 'Festgeldanlage mit fester Laufzeit'),
('Tagesgeld', 'Tagesgeldkonto mit flexibler Verfuegbarkeit'),
('Firmenfestgeld', 'Festgeldanlage fuer Unternehmen'),
('Kinderfestgeld', 'Festgeldanlage fuer Minderjaehrige'),
('Vermoegensverwaltung', 'Professionelle Vermoegensverwaltung'),
('Strategische Beratung', 'Strategische Finanzberatung');

INSERT INTO banks (name, iban_prefix, country) VALUES
('Deutsche Bank AG', 'DE', 'Deutschland'),
('Commerzbank AG', 'DE', 'Deutschland'),
('DZ Bank AG', 'DE', 'Deutschland'),
('Sparkasse Frankfurt', 'DE', 'Deutschland'),
('ING-DiBa AG', 'DE', 'Deutschland'),
('Volksbank Frankfurt', 'DE', 'Deutschland'),
('Santander Consumer Bank', 'DE', 'Deutschland'),
('Comdirect Bank', 'DE', 'Deutschland');

-- Admin: password = Admin@2024 (bcrypt)
INSERT INTO users (email, password_hash, first_name, last_name, is_admin, must_change_password) VALUES
('admin@rockbridgecapital.de', '$2y$12$6qFGTbmAtd0BIXs/w7FtveWz1ZkcO51nSj7OTHG5gCOZO1nCHNCcS', 'Admin', 'Rockbridge', 1, 0);

-- Demo user: password = Demo@2024 (bcrypt)
INSERT INTO users (email, password_hash, first_name, last_name, is_admin, must_change_password) VALUES
('demo@example.com', '$2y$12$VEv/EI/vO5KuEDH8Zgl.uO6FtV45whOz08xmiajNKwAyVUdoF9Sjy', 'Max', 'Mustermann', 0, 1);