-- Backup Logs Database Schema
-- PostgreSQL schema for tracking backup operations

CREATE TABLE backup_logs (
    id              SERIAL PRIMARY KEY,

    -- Timing
    started_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMP,
    duration_ms     INTEGER,

    -- Identifikation
    backup_type     VARCHAR(50) NOT NULL,  -- 'forgejo_repos', 'postgres_db', etc.
    source_name     VARCHAR(255) NOT NULL, -- repo navn eller db navn
    source_path     VARCHAR(500),          -- /var/lib/forgejo/repositories/user/repo.git

    -- Destination
    destination     VARCHAR(50) NOT NULL,  -- 'azure_blob', 's3', 'local', 'sftp'
    remote_path     VARCHAR(500),          -- https://storage.blob.core.windows.net/backups/forgejo/2024-01-31/repo.tar.gz

    -- Resultat
    status          VARCHAR(20) NOT NULL,  -- 'running', 'success', 'failed', 'partial'
    size_bytes      BIGINT,
    file_count      INTEGER,               -- antal filer i backup

    -- Fejlhåndtering
    error_message   TEXT,
    error_code      VARCHAR(50),           -- 'AZURE_UPLOAD_FAILED', 'DISK_FULL', 'TAR_FAILED', etc.
    retry_count     INTEGER DEFAULT 0,

    -- Metadata
    hostname        VARCHAR(100),          -- hvilken server kørte backup
    script_version  VARCHAR(20),           -- version af backup script
    checksum        VARCHAR(64),           -- SHA256 af backup fil

    -- Indexes
    created_at      TIMESTAMP DEFAULT NOW()
);

-- Performance indexes
CREATE INDEX idx_backup_logs_started ON backup_logs(started_at DESC);
CREATE INDEX idx_backup_logs_type ON backup_logs(backup_type);
CREATE INDEX idx_backup_logs_status ON backup_logs(status);
CREATE INDEX idx_backup_logs_source ON backup_logs(source_name);

-- Composite index for repository summary queries
CREATE INDEX idx_backup_logs_source_started ON backup_logs(source_name, started_at DESC);

-- View for repository summaries (optional, can be used for performance)
CREATE OR REPLACE VIEW backup_repository_summary AS
SELECT
    source_name,
    backup_type,
    COUNT(*) as total_backups,
    COUNT(*) FILTER (WHERE status = 'success') as successful_backups,
    COUNT(*) FILTER (WHERE status = 'failed') as failed_backups,
    MAX(started_at) as last_backup,
    MAX(started_at) FILTER (WHERE status = 'success') as last_successful_backup,
    COALESCE(SUM(size_bytes) FILTER (WHERE status = 'success'), 0) as total_size_bytes
FROM backup_logs
GROUP BY source_name, backup_type;

-- Sample data for testing (optional)
-- INSERT INTO backup_logs (backup_type, source_name, source_path, destination, remote_path, status, size_bytes, hostname, script_version)
-- VALUES ('forgejo_repos', 'myorg/myrepo', '/var/lib/forgejo/data/forgejo-repositories/myorg/myrepo.git', 'azure_blob', 'https://storageptadmin.blob.core.windows.net/backups/forgejo/2024-01-31/myorg-myrepo.tar.gz', 'success', 1048576, 'forgejo-server', '2.0.0');

-- Bruger setup til backup script
CREATE USER backup_writer WITH PASSWORD 'your_secure_password_here';

GRANT CONNECT ON DATABASE ptadmin TO backup_writer;
GRANT USAGE ON SCHEMA public TO backup_writer;
GRANT SELECT, INSERT, UPDATE ON backup_logs TO backup_writer;
GRANT USAGE, SELECT ON SEQUENCE backup_logs_id_seq TO backup_writer;