-- Esquema inicial do Bot de Pontos v2 (ARQUITETURA.md, seção 6.2).
-- Executado pelo usuário dono do banco; o usuário da aplicação (botpontos_app)
-- recebe apenas SELECT/INSERT/UPDATE/DELETE no final.

-- +goose Up
CREATE EXTENSION IF NOT EXISTS citext;

-- +goose StatementBegin
CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;
-- +goose StatementEnd

CREATE TYPE user_status         AS ENUM ('pending', 'active', 'disabled');
CREATE TYPE notification_type   AS ENUM ('entrada2', 'saida2');
CREATE TYPE notification_status AS ENUM ('scheduled', 'sending', 'sent', 'failed', 'cancelled', 'expired');
CREATE TYPE web_role            AS ENUM ('owner', 'admin', 'viewer');
CREATE TYPE audit_source        AS ENUM ('telegram', 'web', 'worker', 'cli');
CREATE TYPE day_status          AS ENUM ('awaiting', 'no_points', 'working', 'not_working', 'finished');

-- Pessoa que usa o bot
CREATE TABLE users (
    id                    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    telegram_chat_id      bigint      NOT NULL UNIQUE,
    display_name          text        NOT NULL,
    status                user_status NOT NULL DEFAULT 'pending',
    is_bot_admin          boolean     NOT NULL DEFAULT false,
    notifications_enabled boolean     NOT NULL DEFAULT false,
    -- motivo da pausa dos lembretes, exibido no /status
    notifications_paused_reason text  CHECK (notifications_paused_reason IN ('manual', 'api_failures', 'admin', 'user_disabled')),
    timezone              text        NOT NULL DEFAULT 'America/Sao_Paulo',
    created_at            timestamptz NOT NULL DEFAULT now(),
    updated_at            timestamptz NOT NULL DEFAULT now()
);

-- Regras de jornada e exceção opcional da janela de consulta (9.8 e 9.14)
CREATE TABLE work_schedules (
    user_id                 bigint PRIMARY KEY REFERENCES users(id),
    lunch_minutes           int NOT NULL DEFAULT 60  CHECK (lunch_minutes BETWEEN 15 AND 180),
    daily_work_minutes      int NOT NULL DEFAULT 480 CHECK (daily_work_minutes BETWEEN 60 AND 720),
    entrada2_lead_minutes   int NOT NULL DEFAULT 5   CHECK (entrada2_lead_minutes BETWEEN 0 AND 60),
    saida2_lead_minutes     int NOT NULL DEFAULT 5   CHECK (saida2_lead_minutes BETWEEN 0 AND 60),
    query_window_start      time,
    query_window_end        time,
    day_check_deadline      time,
    no_points_recheck_until time,
    updated_at              timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT work_schedules_window_all_or_none CHECK (
        num_nulls(query_window_start, query_window_end, day_check_deadline, no_points_recheck_until) IN (0, 4)
    )
);

-- Credencial PontoMais (senha cifrada com AES-GCM na aplicação)
CREATE TABLE point_credentials (
    user_id              bigint PRIMARY KEY REFERENCES users(id),
    api_email            text        NOT NULL,
    encrypted_password   bytea       NOT NULL,
    key_version          smallint    NOT NULL,
    last_validated_at    timestamptz,
    last_error           text,
    consecutive_failures int         NOT NULL DEFAULT 0,
    last_failure_at      timestamptz,
    updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE notifications (
    id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id             bigint              NOT NULL REFERENCES users(id),
    reference_date      date                NOT NULL,
    type                notification_type   NOT NULL,
    scheduled_at        timestamptz         NOT NULL,              -- quando o aviso deve sair
    predicted_at        timestamptz         NOT NULL,              -- horário previsto do ponto (vai na mensagem)
    status              notification_status NOT NULL DEFAULT 'scheduled',
    attempts            int                 NOT NULL DEFAULT 0,
    locked_at           timestamptz,
    sent_at             timestamptz,
    telegram_message_id bigint,
    error_message       text,
    created_at          timestamptz NOT NULL DEFAULT now(),
    updated_at          timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, reference_date, type)
);
CREATE INDEX notifications_due_idx ON notifications (scheduled_at) WHERE status = 'scheduled';
CREATE INDEX notifications_sending_idx ON notifications (locked_at) WHERE status = 'sending';
CREATE INDEX notifications_reference_date_idx ON notifications (reference_date);

CREATE TABLE web_accounts (
    id                 bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username           citext      NOT NULL UNIQUE,
    password_hash      text        NOT NULL,
    role               web_role    NOT NULL,
    user_id            bigint      REFERENCES users(id),
    failed_login_count int         NOT NULL DEFAULT 0,
    locked_until       timestamptz,
    disabled_at        timestamptz,
    last_login_at      timestamptz,
    created_at         timestamptz NOT NULL DEFAULT now(),
    updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE web_sessions (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id      bigint      NOT NULL REFERENCES web_accounts(id),
    token_hash      bytea       NOT NULL UNIQUE,
    csrf_token_hash bytea       NOT NULL,
    expires_at      timestamptz NOT NULL,
    last_seen_at    timestamptz NOT NULL,
    revoked_at      timestamptz,
    user_agent      text,
    ip              inet,
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX web_sessions_expires_idx ON web_sessions (expires_at);

-- Feriados vindos da API e cadastrados manualmente (9.9)
CREATE TABLE holidays (
    day        date PRIMARY KEY,
    name       text NOT NULL,
    source     text NOT NULL CHECK (source IN ('feriados.dev', 'manual')),
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Situação de cada usuário em cada dia e próxima consulta (9.12)
CREATE TABLE user_days (
    user_id           bigint      NOT NULL REFERENCES users(id),
    day               date        NOT NULL,
    status            day_status  NOT NULL DEFAULT 'awaiting',
    zero_point_checks smallint    NOT NULL DEFAULT 0,
    last_point_count  smallint    NOT NULL DEFAULT 0,
    next_check_at     timestamptz,
    resumed_by        text CHECK (resumed_by IN ('ativarponto', 'entrada2', 'saida2', 'web')),
    updated_at        timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, day)
);
CREATE INDEX user_days_next_check_idx ON user_days (next_check_at) WHERE next_check_at IS NOT NULL;

-- Configurações globais editáveis pelo painel (9.13)
CREATE TABLE app_settings (
    key                text PRIMARY KEY,
    value              jsonb       NOT NULL,
    updated_by_account bigint      REFERENCES web_accounts(id),
    updated_at         timestamptz NOT NULL DEFAULT now()
);

-- Estado operacional do worker (ex.: queda da PontoMais em andamento)
CREATE TABLE service_state (
    key        text PRIMARY KEY,
    value      jsonb       NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE audit_logs (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    occurred_at    timestamptz  NOT NULL DEFAULT now(),
    source         audit_source NOT NULL,
    actor_user_id  bigint REFERENCES users(id),
    actor_account  bigint REFERENCES web_accounts(id),
    target_user_id bigint REFERENCES users(id),
    event_type     text    NOT NULL,
    success        boolean NOT NULL,
    details        jsonb   NOT NULL DEFAULT '{}'
);
CREATE INDEX audit_logs_time_idx   ON audit_logs (occurred_at DESC);
CREATE INDEX audit_logs_target_idx ON audit_logs (target_user_id, occurred_at DESC);

CREATE TRIGGER users_set_updated_at             BEFORE UPDATE ON users             FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER work_schedules_set_updated_at    BEFORE UPDATE ON work_schedules    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- point_credentials não usa o trigger: updated_at marca só a troca da credencial (e invalida o
-- cache de sessão da PontoMais); contadores de falha não devem forçar novo login.
CREATE TRIGGER notifications_set_updated_at     BEFORE UPDATE ON notifications     FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER web_accounts_set_updated_at      BEFORE UPDATE ON web_accounts      FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER user_days_set_updated_at         BEFORE UPDATE ON user_days         FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER app_settings_set_updated_at      BEFORE UPDATE ON app_settings      FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER service_state_set_updated_at     BEFORE UPDATE ON service_state     FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Permissões do usuário da aplicação (criado por postgres/init/01-create-app-role.sh).
-- As tabelas de migrations futuras herdam as permissões pelo DEFAULT PRIVILEGES.
-- +goose StatementBegin
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'botpontos_app') THEN
        GRANT USAGE ON SCHEMA public TO botpontos_app;
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO botpontos_app;
        GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO botpontos_app;
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO botpontos_app;
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO botpontos_app;
        -- A aplicação não altera o controle de versão das migrations.
        REVOKE INSERT, UPDATE, DELETE ON goose_db_version FROM botpontos_app;
    ELSE
        RAISE NOTICE 'role botpontos_app não existe; permissões da aplicação não aplicadas';
    END IF;
END;
$$;
-- +goose StatementEnd

-- +goose Down
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS service_state;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS user_days;
DROP TABLE IF EXISTS holidays;
DROP TABLE IF EXISTS web_sessions;
DROP TABLE IF EXISTS web_accounts;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS point_credentials;
DROP TABLE IF EXISTS work_schedules;
DROP TABLE IF EXISTS users;
DROP TYPE IF EXISTS day_status;
DROP TYPE IF EXISTS audit_source;
DROP TYPE IF EXISTS web_role;
DROP TYPE IF EXISTS notification_status;
DROP TYPE IF EXISTS notification_type;
DROP TYPE IF EXISTS user_status;
DROP FUNCTION IF EXISTS set_updated_at();