-- 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 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 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');
