CREATE TABLE IF NOT EXISTS applications (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL,
    name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    description TEXT,
    app_type TEXT NOT NULL,
    provider TEXT NOT NULL,
    status TEXT NOT NULL,
    version TEXT,
    install_path TEXT,
    work_dir TEXT,
    config_paths_json TEXT NOT NULL DEFAULT '[]',
    log_paths_json TEXT NOT NULL DEFAULT '[]',
    data_paths_json TEXT NOT NULL DEFAULT '[]',
    ports_json TEXT NOT NULL DEFAULT '[]',
    domains_json TEXT NOT NULL DEFAULT '[]',
    service_name TEXT,
    container_id TEXT,
    compose_project TEXT,
    package_name TEXT,
    nginx_site TEXT,
    run_user TEXT,
    is_system INTEGER NOT NULL DEFAULT 0,
    is_managed INTEGER NOT NULL DEFAULT 0,
    is_lightops_managed INTEGER NOT NULL DEFAULT 0,
    metadata_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_applications_agent ON applications(agent_id);
CREATE INDEX IF NOT EXISTS idx_applications_provider ON applications(provider);
CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status);

CREATE TABLE IF NOT EXISTS application_relations (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL,
    app_id TEXT NOT NULL,
    relation_type TEXT NOT NULL,
    target_id TEXT,
    target_name TEXT,
    metadata_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL,
    FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE,
    FOREIGN KEY(app_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_application_relations_app ON application_relations(app_id);

CREATE TABLE IF NOT EXISTS application_actions (
    id TEXT PRIMARY KEY,
    agent_id TEXT NOT NULL,
    app_id TEXT,
    user_id TEXT,
    action TEXT NOT NULL,
    status TEXT NOT NULL,
    params_json TEXT,
    result_json TEXT,
    error TEXT,
    created_at TEXT NOT NULL,
    finished_at TEXT,
    FOREIGN KEY(agent_id) REFERENCES agents(id) ON DELETE CASCADE,
    FOREIGN KEY(app_id) REFERENCES applications(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_application_actions_app_created ON application_actions(app_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_application_actions_agent_created ON application_actions(agent_id, created_at DESC);