-- IliaCloud — Migration initiale
-- Lancer avec : node src/db/migrate.js

-- Extension UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- LOG PATHS
-- ============================================================
CREATE TABLE IF NOT EXISTS log_paths (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  label     TEXT NOT NULL,
  path      TEXT NOT NULL,
  type      TEXT NOT NULL DEFAULT 'file' CHECK (type IN ('file', 'docker', 'docker-file')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_log_paths_server ON log_paths(server_id);

-- ============================================================
-- QUICK ACTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS quick_actions (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id        UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  label            TEXT NOT NULL,
  command          TEXT NOT NULL,
  requires_confirm BOOLEAN NOT NULL DEFAULT FALSE,
  sort_order       INTEGER NOT NULL DEFAULT 0,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_quick_actions_server ON quick_actions(server_id);

-- ============================================================
-- CHAT SESSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS chat_sessions (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  server_id UUID REFERENCES servers(id) ON DELETE SET NULL,
  title     TEXT NOT NULL DEFAULT 'Nouvelle session',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_user ON chat_sessions(user_id);

-- ============================================================
-- METRICS HISTORY
-- ============================================================
-- Stocke les metriques serveur toutes les 5 minutes.
-- Utilise pour les graphiques historiques (1h, 24h, 7j, 30j).
-- Nettoyage automatique des donnees > 30 jours via un job periodique.
CREATE TABLE IF NOT EXISTS metrics_history (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id  UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  cpu_percent     SMALLINT,
  mem_percent     SMALLINT,
  mem_used_mb     INTEGER,
  mem_total_mb    INTEGER,
  disk_percent    SMALLINT,
  load_1m         REAL,
  load_5m         REAL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_metrics_history_server_time ON metrics_history(server_id, created_at DESC);

-- ============================================================
-- PUSH SUBSCRIPTIONS (Web Push)
-- ============================================================
-- Stocke les abonnements push de chaque navigateur/appareil.
-- Un utilisateur peut avoir plusieurs subscriptions (mobile + desktop).
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  endpoint    TEXT NOT NULL UNIQUE,
  keys_p256dh TEXT NOT NULL,
  keys_auth   TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_push_subs_user ON push_subscriptions(user_id);

-- ============================================================
-- ALERT RULES (seuils de notification)
-- ============================================================
-- Configure les seuils par serveur. Quand une metrique depasse le seuil,
-- une notification push est envoyee.
CREATE TABLE IF NOT EXISTS alert_rules (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  metric      TEXT NOT NULL CHECK (metric IN ('cpu', 'mem', 'disk', 'load', 'container_down')),
  threshold   INTEGER NOT NULL DEFAULT 90,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(server_id, user_id, metric)
);
CREATE INDEX IF NOT EXISTS idx_alert_rules_server ON alert_rules(server_id);

-- ============================================================
-- ALERT HISTORY (historique des alertes)
-- ============================================================
CREATE TABLE IF NOT EXISTS alert_history (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_rule_id UUID REFERENCES alert_rules(id) ON DELETE SET NULL,
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  metric      TEXT NOT NULL,
  value       INTEGER,
  threshold   INTEGER,
  message     TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_alert_history_user ON alert_history(user_id, created_at DESC);

-- ============================================================
-- UPTIME MONITORS
-- ============================================================
-- URLs a surveiller avec intervalle configurable.
CREATE TABLE IF NOT EXISTS uptime_monitors (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  url         TEXT NOT NULL,
  label       TEXT NOT NULL,
  interval_s  INTEGER NOT NULL DEFAULT 300,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_uptime_monitors_user ON uptime_monitors(user_id);

-- ============================================================
-- UPTIME HISTORY
-- ============================================================
-- Chaque ping est enregistre avec son statut et sa latence.
-- Nettoyage automatique des donnees > 30 jours.
CREATE TABLE IF NOT EXISTS uptime_history (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  monitor_id  UUID NOT NULL REFERENCES uptime_monitors(id) ON DELETE CASCADE,
  status      INTEGER NOT NULL,
  latency_ms  INTEGER,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_uptime_history_monitor_time ON uptime_history(monitor_id, created_at DESC);

-- ============================================================
-- BACKUPS
-- ============================================================
CREATE TABLE IF NOT EXISTS backups (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  label       TEXT NOT NULL,
  db_type     TEXT NOT NULL CHECK (db_type IN ('postgresql', 'mysql')),
  db_name     TEXT NOT NULL,
  db_user     TEXT NOT NULL DEFAULT 'root',
  db_container TEXT,
  file_path   TEXT,
  file_size   BIGINT,
  status      TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'done', 'error')),
  error       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Configs de backup automatique
CREATE TABLE IF NOT EXISTS backup_schedules (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  db_type     TEXT NOT NULL CHECK (db_type IN ('postgresql', 'mysql')),
  db_name     TEXT NOT NULL,
  db_user     TEXT NOT NULL DEFAULT 'root',
  db_container TEXT,
  frequency   TEXT NOT NULL DEFAULT 'daily' CHECK (frequency IN ('hourly', 'daily', 'weekly')),
  retention   INTEGER NOT NULL DEFAULT 7,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  last_run    TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_backup_schedules_server ON backup_schedules(server_id);
CREATE INDEX IF NOT EXISTS idx_backups_server ON backups(server_id, created_at DESC);

-- ============================================================
-- WEBHOOKS
-- ============================================================
CREATE TABLE IF NOT EXISTS webhooks (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label       TEXT NOT NULL,
  url         TEXT NOT NULL,
  platform    TEXT NOT NULL DEFAULT 'discord' CHECK (platform IN ('discord', 'slack', 'telegram', 'custom')),
  events      TEXT[] NOT NULL DEFAULT '{server_alert,server_recovery,uptime_down,uptime_up,backup_error,backup_success,ssl_expiring,ssl_expired,ssl_recovery,http_error}',
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_webhooks_user ON webhooks(user_id);

-- ============================================================
-- SSL CERTIFICATES (monitoring des certificats HTTPS)
-- ============================================================
-- Surveille la date d'expiration des certificats SSL/TLS.
-- Peut etre lie a un uptime_monitor (scan auto) ou ajoute manuellement.
CREATE TABLE IF NOT EXISTS ssl_certificates (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  domain        TEXT NOT NULL,
  port          INTEGER NOT NULL DEFAULT 443,
  issuer        TEXT,
  subject       TEXT,
  valid_from    TIMESTAMPTZ,
  valid_to      TIMESTAMPTZ,
  alert_days    INTEGER NOT NULL DEFAULT 30,
  enabled       BOOLEAN NOT NULL DEFAULT TRUE,
  last_check    TIMESTAMPTZ,
  last_status   TEXT DEFAULT 'pending' CHECK (last_status IN ('valid', 'expiring', 'expired', 'error', 'pending')),
  last_error    TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ssl_certificates_user ON ssl_certificates(user_id);

-- ============================================================
-- SSL CHECK HISTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS ssl_check_history (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  certificate_id  UUID NOT NULL REFERENCES ssl_certificates(id) ON DELETE CASCADE,
  status          TEXT NOT NULL CHECK (status IN ('valid', 'expiring', 'expired', 'error')),
  days_remaining  INTEGER,
  error           TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ssl_check_history_cert_time ON ssl_check_history(certificate_id, created_at DESC);

-- ============================================================
-- STATUS PAGES (dashboards publics de statut)
-- ============================================================
-- Chaque utilisateur peut creer une ou plusieurs pages de statut publiques.
-- Accessible sans authentification via un slug unique.
CREATE TABLE IF NOT EXISTS status_pages (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  slug        TEXT NOT NULL UNIQUE,
  title       TEXT NOT NULL DEFAULT 'Statut des services',
  description TEXT,
  logo_url    TEXT,
  banner_url  TEXT,
  theme       TEXT NOT NULL DEFAULT 'default',
  custom_css  TEXT,
  accent_color TEXT NOT NULL DEFAULT '#6366f1',
  bg_color    TEXT NOT NULL DEFAULT '#0f172a',
  text_color  TEXT NOT NULL DEFAULT '#f3f4f6',
  show_uptime  BOOLEAN NOT NULL DEFAULT TRUE,
  show_latency BOOLEAN NOT NULL DEFAULT TRUE,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_status_pages_user ON status_pages(user_id);
CREATE INDEX IF NOT EXISTS idx_status_pages_slug ON status_pages(slug);

-- ============================================================
-- STATUS PAGE MONITORS (services affiches sur la page)
-- ============================================================
CREATE TABLE IF NOT EXISTS status_page_monitors (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status_page_id  UUID NOT NULL REFERENCES status_pages(id) ON DELETE CASCADE,
  monitor_id      UUID NOT NULL REFERENCES uptime_monitors(id) ON DELETE CASCADE,
  label           TEXT,
  sort_order      INTEGER NOT NULL DEFAULT 0,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_status_page_monitors_page ON status_page_monitors(status_page_id);

-- ============================================================
-- AUDIT LOGS
-- ============================================================
-- Trace toutes les actions utilisateur pour la securite et le debug.
CREATE TABLE IF NOT EXISTS audit_logs (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  action      TEXT NOT NULL,
  category    TEXT NOT NULL DEFAULT 'general' CHECK (category IN ('general','auth','server','docker','ssh','ssh_key','chat','settings','backup','alert','file','cron','webhook','ssl','uptime','status_page','export','annotation','terminal','digest','report','2fa','billing','api')),
  target      TEXT,
  details     JSONB,
  ip          TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_time ON audit_logs(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_category ON audit_logs(category, created_at DESC);

-- ============================================================
-- PLANS (limites et features configurables)
-- ============================================================
-- Les limites des plans sont stockees en JSONB pour pouvoir etre
-- modifiees sans redeployer. Le backend charge ces valeurs au
-- demarrage et les rafraichit toutes les 5 minutes.
CREATE TABLE IF NOT EXISTS plans (
  name        TEXT PRIMARY KEY CHECK (name IN ('free', 'pro', 'business', 'enterprise')),
  limits      JSONB NOT NULL DEFAULT '{}',
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed des plans par defaut
INSERT INTO plans (name, limits) VALUES
  ('free', '{
    "servers": 3,
    "uptime_monitors": 10,
    "uptime_interval_seconds": 300,
    "ssl_certificates": 10,
    "webhooks": 2,
    "alert_rules": 5,
    "quick_actions": 10,
    "log_paths": 10,
    "backup_schedules": 2,
    "backup_manual_per_day": 1,
    "backup_retention_days": 3,
    "chat_messages_per_day": 30,
    "chat_sessions": 5,
    "status_pages": 1,
    "metrics_retention_days": 7,
    "audit_retention_days": 7,
    "terminal_ssh": false,
    "file_editor_write": false,
    "docker_stats": false,
    "docker_logs_streaming": false,
    "docker_stacks": false,
    "docker_prune": false,
    "cron_write": false,
    "backup_download": false,
    "backup_restore": false,
    "chat_tool_use": false,
    "export_config": false,
    "export_csv_audit": false,
    "http_error_alerts": false,
    "ssl_alerts": false,
    "email_digest": false,
    "monthly_report": false,
    "status_page_whitelabel": false,
    "api_access": false,
    "api_requests_per_hour": 0,
    "users": 1
  }'),
  ('pro', '{
    "servers": 10,
    "uptime_monitors": 50,
    "uptime_interval_seconds": 60,
    "ssl_certificates": 50,
    "webhooks": 10,
    "alert_rules": 50,
    "quick_actions": -1,
    "log_paths": -1,
    "backup_schedules": 20,
    "backup_manual_per_day": -1,
    "backup_retention_days": 30,
    "chat_messages_per_day": 500,
    "chat_sessions": -1,
    "status_pages": 5,
    "metrics_retention_days": 90,
    "audit_retention_days": 90,
    "terminal_ssh": true,
    "file_editor_write": true,
    "docker_stats": true,
    "docker_logs_streaming": true,
    "docker_stacks": true,
    "docker_prune": true,
    "cron_write": true,
    "backup_download": true,
    "backup_restore": true,
    "chat_tool_use": true,
    "export_config": true,
    "export_csv_audit": true,
    "http_error_alerts": true,
    "ssl_alerts": true,
    "email_digest": true,
    "monthly_report": true,
    "status_page_whitelabel": false,
    "api_access": true,
    "api_requests_per_hour": 1000,
    "users": 1
  }'),
  ('business', '{
    "servers": 30,
    "uptime_monitors": 150,
    "uptime_interval_seconds": 30,
    "ssl_certificates": 150,
    "webhooks": 30,
    "alert_rules": 150,
    "quick_actions": -1,
    "log_paths": -1,
    "backup_schedules": 50,
    "backup_manual_per_day": -1,
    "backup_retention_days": 90,
    "chat_messages_per_day": 2000,
    "chat_sessions": -1,
    "status_pages": 20,
    "metrics_retention_days": 180,
    "audit_retention_days": 180,
    "terminal_ssh": true,
    "file_editor_write": true,
    "docker_stats": true,
    "docker_logs_streaming": true,
    "docker_stacks": true,
    "docker_prune": true,
    "cron_write": true,
    "backup_download": true,
    "backup_restore": true,
    "chat_tool_use": true,
    "export_config": true,
    "export_csv_audit": true,
    "http_error_alerts": true,
    "ssl_alerts": true,
    "email_digest": true,
    "monthly_report": true,
    "status_page_whitelabel": false,
    "api_access": true,
    "api_requests_per_hour": 5000,
    "users": 3
  }'),
  ('enterprise', '{
    "servers": -1,
    "uptime_monitors": -1,
    "uptime_interval_seconds": 30,
    "ssl_certificates": -1,
    "webhooks": -1,
    "alert_rules": -1,
    "quick_actions": -1,
    "log_paths": -1,
    "backup_schedules": -1,
    "backup_manual_per_day": -1,
    "backup_retention_days": 365,
    "chat_messages_per_day": -1,
    "chat_sessions": -1,
    "status_pages": -1,
    "metrics_retention_days": 365,
    "audit_retention_days": 365,
    "terminal_ssh": true,
    "file_editor_write": true,
    "docker_stats": true,
    "docker_logs_streaming": true,
    "docker_stacks": true,
    "docker_prune": true,
    "cron_write": true,
    "backup_download": true,
    "backup_restore": true,
    "chat_tool_use": true,
    "export_config": true,
    "export_csv_audit": true,
    "http_error_alerts": true,
    "ssl_alerts": true,
    "email_digest": true,
    "monthly_report": true,
    "status_page_whitelabel": true,
    "api_access": true,
    "api_requests_per_hour": 20000,
    "users": -1
  }')
ON CONFLICT (name) DO UPDATE SET limits = EXCLUDED.limits;

-- ============================================================
-- SUBSCRIPTIONS (abonnements Stripe)
-- ============================================================
CREATE TABLE IF NOT EXISTS subscriptions (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id                 UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  stripe_customer_id      TEXT UNIQUE,
  stripe_subscription_id  TEXT UNIQUE,
  stripe_price_id         TEXT,
  plan                    TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'business', 'enterprise')),
  status                  TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'canceled', 'past_due', 'trialing', 'incomplete')),
  current_period_start    TIMESTAMPTZ,
  current_period_end      TIMESTAMPTZ,
  cancel_at_period_end    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_subscriptions_user ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_stripe ON subscriptions(stripe_customer_id);

-- Codes de secours (hashes, usage unique)
CREATE TABLE IF NOT EXISTS totp_backup_codes (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash TEXT NOT NULL,
  used      BOOLEAN NOT NULL DEFAULT FALSE,
  used_at   TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_totp_backup_codes_user ON totp_backup_codes(user_id);

-- ============================================================
-- ANNOTATIONS (incidents / evenements sur la timeline)
-- ============================================================
-- Permet de marquer des evenements sur les graphiques de metriques.
-- Ex: "deploiement v2.3", "pic RAM cause par X", "maintenance reseau"
CREATE TABLE IF NOT EXISTS annotations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       VARCHAR(200) NOT NULL,
  description TEXT,
  category    VARCHAR(20) NOT NULL DEFAULT 'other'
              CHECK (category IN ('deployment', 'incident', 'maintenance', 'config', 'other')),
  timestamp   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_annotations_server_time ON annotations(server_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_annotations_user ON annotations(user_id);

-- ============================================================
-- AUDIT LOGS — ajout categories manquantes
-- ============================================================
ALTER TABLE audit_logs DROP CONSTRAINT IF EXISTS audit_logs_category_check;
ALTER TABLE audit_logs ADD CONSTRAINT audit_logs_category_check
  CHECK (category IN ('general','auth','server','docker','ssh','ssh_key','chat','settings','backup','alert','file','cron','webhook','ssl','uptime','status_page','export','annotation','terminal','digest','report','2fa','billing','api'));

-- ============================================================
-- PLANS — ajout features digest et report
-- ============================================================
UPDATE plans SET limits = limits || '{"email_digest": false, "monthly_report": false}' WHERE name = 'free' AND NOT (limits ? 'email_digest');
UPDATE plans SET limits = limits || '{"email_digest": true, "monthly_report": true}' WHERE name = 'pro' AND NOT (limits ? 'email_digest');
UPDATE plans SET limits = limits || '{"email_digest": true, "monthly_report": true}' WHERE name = 'business' AND NOT (limits ? 'email_digest');
UPDATE plans SET limits = limits || '{"email_digest": true, "monthly_report": true}' WHERE name = 'enterprise' AND NOT (limits ? 'email_digest');

-- ============================================================
-- STATUS PAGES — ajout banner_url
-- ============================================================
ALTER TABLE status_pages ADD COLUMN IF NOT EXISTS banner_url TEXT;
ALTER TABLE status_pages ADD COLUMN IF NOT EXISTS theme TEXT NOT NULL DEFAULT 'default';
ALTER TABLE status_pages ADD COLUMN IF NOT EXISTS custom_css TEXT;

-- ============================================================
-- PLANS — mise a jour des limites (generosites avril 2026)
-- ============================================================
UPDATE plans SET limits = '{
  "servers": 3, "uptime_monitors": 10, "uptime_interval_seconds": 300,
  "ssl_certificates": 10, "webhooks": 2, "alert_rules": 5,
  "quick_actions": 10, "log_paths": 10, "backup_schedules": 2,
  "backup_manual_per_day": 1, "backup_retention_days": 3,
  "chat_messages_per_day": 30, "chat_sessions": 5, "status_pages": 1,
  "metrics_retention_days": 7, "audit_retention_days": 7,
  "terminal_ssh": false, "file_editor_write": false,
  "docker_stats": false, "docker_logs_streaming": false,
  "docker_stacks": false, "docker_prune": false, "cron_write": false,
  "backup_download": false, "backup_restore": false,
  "chat_tool_use": false, "export_config": false, "export_csv_audit": false,
  "http_error_alerts": false, "ssl_alerts": false,
  "email_digest": false, "monthly_report": false,
  "status_page_whitelabel": false, "api_access": false, "api_requests_per_hour": 0, "users": 1
}'::jsonb WHERE name = 'free';

UPDATE plans SET limits = '{
  "servers": 10, "uptime_monitors": 50, "uptime_interval_seconds": 60,
  "ssl_certificates": 50, "webhooks": 10, "alert_rules": 50,
  "quick_actions": -1, "log_paths": -1, "backup_schedules": 20,
  "backup_manual_per_day": -1, "backup_retention_days": 30,
  "chat_messages_per_day": 500, "chat_sessions": -1, "status_pages": 5,
  "metrics_retention_days": 90, "audit_retention_days": 90,
  "terminal_ssh": true, "file_editor_write": true,
  "docker_stats": true, "docker_logs_streaming": true,
  "docker_stacks": true, "docker_prune": true, "cron_write": true,
  "backup_download": true, "backup_restore": true,
  "chat_tool_use": true, "export_config": true, "export_csv_audit": true,
  "http_error_alerts": true, "ssl_alerts": true,
  "email_digest": true, "monthly_report": true,
  "status_page_whitelabel": false, "api_access": true, "api_requests_per_hour": 1000, "users": 1
}'::jsonb WHERE name = 'pro';

UPDATE plans SET limits = '{
  "servers": 30, "uptime_monitors": 150, "uptime_interval_seconds": 30,
  "ssl_certificates": 150, "webhooks": 30, "alert_rules": 150,
  "quick_actions": -1, "log_paths": -1, "backup_schedules": 50,
  "backup_manual_per_day": -1, "backup_retention_days": 90,
  "chat_messages_per_day": 2000, "chat_sessions": -1, "status_pages": 20,
  "metrics_retention_days": 180, "audit_retention_days": 180,
  "terminal_ssh": true, "file_editor_write": true,
  "docker_stats": true, "docker_logs_streaming": true,
  "docker_stacks": true, "docker_prune": true, "cron_write": true,
  "backup_download": true, "backup_restore": true,
  "chat_tool_use": true, "export_config": true, "export_csv_audit": true,
  "http_error_alerts": true, "ssl_alerts": true,
  "email_digest": true, "monthly_report": true,
  "status_page_whitelabel": false, "api_access": true, "api_requests_per_hour": 5000, "users": 3
}'::jsonb WHERE name = 'business';

INSERT INTO plans (name, limits) VALUES ('enterprise', '{
  "servers": -1, "uptime_monitors": -1, "uptime_interval_seconds": 30,
  "ssl_certificates": -1, "webhooks": -1, "alert_rules": -1,
  "quick_actions": -1, "log_paths": -1, "backup_schedules": -1,
  "backup_manual_per_day": -1, "backup_retention_days": 365,
  "chat_messages_per_day": -1, "chat_sessions": -1, "status_pages": -1,
  "metrics_retention_days": 365, "audit_retention_days": 365,
  "terminal_ssh": true, "file_editor_write": true,
  "docker_stats": true, "docker_logs_streaming": true,
  "docker_stacks": true, "docker_prune": true, "cron_write": true,
  "backup_download": true, "backup_restore": true,
  "chat_tool_use": true, "export_config": true, "export_csv_audit": true,
  "http_error_alerts": true, "ssl_alerts": true,
  "email_digest": true, "monthly_report": true,
  "status_page_whitelabel": true, "api_access": true, "api_requests_per_hour": 20000, "users": -1
}'::jsonb) ON CONFLICT (name) DO UPDATE SET limits = EXCLUDED.limits;

-- ============================================================
-- PARTAGE D'ACCES — Groupes
-- ============================================================
CREATE TABLE IF NOT EXISTS access_groups (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  slug          TEXT NOT NULL,
  description   TEXT,
  color         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ,
  UNIQUE(owner_user_id, slug)
);
CREATE INDEX IF NOT EXISTS idx_access_groups_owner ON access_groups(owner_user_id);

-- ============================================================
-- PARTAGE D'ACCES — Membres
-- ============================================================
CREATE TABLE IF NOT EXISTS access_group_members (
  id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id           UUID NOT NULL REFERENCES access_groups(id) ON DELETE CASCADE,
  user_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  invited_by_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status             TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'revoked')),
  permissions        JSONB NOT NULL DEFAULT '{}'::jsonb
                    CHECK (is_valid_group_permissions(permissions)),
  joined_at          TIMESTAMPTZ,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at         TIMESTAMPTZ,
  UNIQUE(group_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_access_group_members_group ON access_group_members(group_id);
CREATE INDEX IF NOT EXISTS idx_access_group_members_user ON access_group_members(user_id);

-- ============================================================
-- PARTAGE D'ACCES — Serveurs lies aux groupes
-- ============================================================
CREATE TABLE IF NOT EXISTS access_group_servers (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id    UUID NOT NULL REFERENCES access_groups(id) ON DELETE CASCADE,
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(group_id, server_id)
);
CREATE INDEX IF NOT EXISTS idx_access_group_servers_group ON access_group_servers(group_id);
CREATE INDEX IF NOT EXISTS idx_access_group_servers_server ON access_group_servers(server_id);

-- ============================================================
-- PARTAGE D'ACCES — Targets Docker par groupe
-- ============================================================
CREATE TABLE IF NOT EXISTS access_group_docker_targets (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id      UUID NOT NULL REFERENCES access_groups(id) ON DELETE CASCADE,
  server_id     UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  target_type   TEXT NOT NULL CHECK (target_type IN ('container', 'service', 'compose_project', 'stack')),
  match_method  TEXT NOT NULL DEFAULT 'name' CHECK (match_method IN ('name', 'label')),
  selector      TEXT NOT NULL,
  label         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(group_id, server_id, target_type, match_method, selector)
);
CREATE INDEX IF NOT EXISTS idx_access_group_docker_targets_group ON access_group_docker_targets(group_id);
CREATE INDEX IF NOT EXISTS idx_access_group_docker_targets_server ON access_group_docker_targets(server_id);

-- ============================================================
-- PARTAGE D'ACCES — Racines fichiers par groupe
-- ============================================================
CREATE TABLE IF NOT EXISTS access_group_file_roots (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id    UUID NOT NULL REFERENCES access_groups(id) ON DELETE CASCADE,
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  label       TEXT NOT NULL,
  root_path   TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(group_id, server_id, root_path)
);
CREATE INDEX IF NOT EXISTS idx_access_group_file_roots_group ON access_group_file_roots(group_id);
CREATE INDEX IF NOT EXISTS idx_access_group_file_roots_server ON access_group_file_roots(server_id);

-- ============================================================
-- PARTAGE D'ACCES — Entrees cron gerees par IliaCloud
-- ============================================================
CREATE TABLE IF NOT EXISTS access_group_cron_entries (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_id    UUID NOT NULL REFERENCES access_groups(id) ON DELETE CASCADE,
  server_id   UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
  target_user TEXT NOT NULL DEFAULT 'user' CHECK (target_user IN ('user', 'root')),
  schedule    TEXT NOT NULL,
  command     TEXT NOT NULL,
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  marker      TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_access_group_cron_entries_group ON access_group_cron_entries(group_id);
CREATE INDEX IF NOT EXISTS idx_access_group_cron_entries_server ON access_group_cron_entries(server_id);

-- ============================================================
-- PARTAGE D'ACCES — Colonnes group_id sur tables existantes
-- ============================================================
-- Phase A : colonnes NULLABLE (pas de NOT NULL avant backfill complet)
ALTER TABLE log_paths         ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE quick_actions     ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE chat_sessions     ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE alert_rules       ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE alert_history     ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE backups           ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE backup_schedules  ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE uptime_monitors   ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE webhooks          ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE ssl_certificates  ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE status_pages      ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE annotations       ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;

-- Index sur group_id
CREATE INDEX IF NOT EXISTS idx_log_paths_group ON log_paths(group_id);
CREATE INDEX IF NOT EXISTS idx_quick_actions_group ON quick_actions(group_id);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_group ON chat_sessions(group_id);
CREATE INDEX IF NOT EXISTS idx_alert_rules_group ON alert_rules(group_id);
CREATE INDEX IF NOT EXISTS idx_alert_history_group_time ON alert_history(group_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_backups_group_time ON backups(group_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_backup_schedules_group ON backup_schedules(group_id);
CREATE INDEX IF NOT EXISTS idx_uptime_monitors_group ON uptime_monitors(group_id);
CREATE INDEX IF NOT EXISTS idx_webhooks_group ON webhooks(group_id);
CREATE INDEX IF NOT EXISTS idx_ssl_certificates_group ON ssl_certificates(group_id);
CREATE INDEX IF NOT EXISTS idx_status_pages_group ON status_pages(group_id);
CREATE INDEX IF NOT EXISTS idx_annotations_group_time ON annotations(group_id, timestamp DESC);

-- ============================================================
-- PARTAGE D'ACCES — Evolution audit_logs
-- ============================================================
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS owner_user_id UUID REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS resource_type TEXT;
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS resource_id UUID;

-- ============================================================
-- PLANS — ajout groups_per_server
-- ============================================================
UPDATE plans SET limits = limits || '{"groups_per_server": 0}'::jsonb WHERE name = 'free' AND NOT (limits ? 'groups_per_server');
UPDATE plans SET limits = limits || '{"groups_per_server": 0}'::jsonb WHERE name = 'pro' AND NOT (limits ? 'groups_per_server');
UPDATE plans SET limits = limits || '{"groups_per_server": 10}'::jsonb WHERE name = 'business' AND NOT (limits ? 'groups_per_server');
UPDATE plans SET limits = limits || '{"groups_per_server": 20}'::jsonb WHERE name = 'enterprise' AND NOT (limits ? 'groups_per_server');

-- ============================================================
-- PARTAGE D'ACCES — Migration soft-delete → hard-delete
-- ============================================================
-- Supprimer la colonne deleted_at (plus de soft-delete)
ALTER TABLE access_groups DROP COLUMN IF EXISTS deleted_at;

-- ============================================================
-- PARTAGE D'ACCES — Scoping group_id sur les ressources
-- ============================================================
-- Permet d'assigner chaque ressource a un groupe pour que les membres
-- ne voient que les ressources de leur groupe.

ALTER TABLE uptime_monitors ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_uptime_monitors_group ON uptime_monitors(group_id);

-- Service Docker associe (rempli automatiquement via scan Traefik)
ALTER TABLE uptime_monitors ADD COLUMN IF NOT EXISTS docker_service TEXT;
ALTER TABLE ssl_certificates ADD COLUMN IF NOT EXISTS docker_service TEXT;

-- Container cible pour les actions rapides (pour execution via docker exec)
ALTER TABLE quick_actions ADD COLUMN IF NOT EXISTS container_id TEXT;

ALTER TABLE ssl_certificates ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_ssl_certificates_group ON ssl_certificates(group_id);

ALTER TABLE alert_rules ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_alert_rules_group ON alert_rules(group_id);

ALTER TABLE webhooks ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_webhooks_group ON webhooks(group_id);

ALTER TABLE status_pages ADD COLUMN IF NOT EXISTS group_id UUID REFERENCES access_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_status_pages_group ON status_pages(group_id);
