-- PRManage WebUI Database Schema
-- MySQL 5.7+ / MySQL 8.0+

CREATE DATABASE IF NOT EXISTS prmanage CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE prmanage;

-- Deployments table
CREATE TABLE IF NOT EXISTS deployments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    repository_id INT UNSIGNED NOT NULL,
    branch_name VARCHAR(255) NOT NULL,
    server_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    port INT UNSIGNED,
    status ENUM('queued', 'running', 'success', 'failed', 'destroyed') NOT NULL DEFAULT 'queued',
    log_file VARCHAR(500),
    url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (repository_id) REFERENCES repositories(id) ON DELETE CASCADE,
    FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_user_id (user_id),
    INDEX idx_repository_id (repository_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit logs table
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    action VARCHAR(100) NOT NULL,
    deployment_id INT UNSIGNED,
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_action (action),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
