-- =============================================================================
-- Tarea 1.2 - Base central PrixusSyncHub (servidor SQL central)
-- Cola sellada de eventos, secuencias por alcance, registro de cajas,
-- banderazos y permisos de publicación. Idempotente; rollback al final.
-- =============================================================================

IF DB_ID(N'PrixusSyncHub') IS NULL
    CREATE DATABASE PrixusSyncHub;
GO

USE PrixusSyncHub;
GO

-- Cola sellada: fuente de verdad del stream. PK por alcance+secuencia,
-- event_id único para idempotencia extremo a extremo.
IF OBJECT_ID(N'dbo.PriSyncEventos', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncEventos
    (
        scope_key     VARCHAR(64)      NOT NULL,
        seq           BIGINT           NOT NULL,
        event_id      UNIQUEIDENTIFIER NOT NULL,
        entity_type   VARCHAR(32)      NOT NULL,
        entity_key    VARCHAR(64)      NULL,
        operation     VARCHAR(10)      NOT NULL,
        origin_json   NVARCHAR(400)    NOT NULL,
        payload_json  NVARCHAR(MAX)    NOT NULL,
        payload_hash  CHAR(71)         NOT NULL,
        contract_id   INT              NOT NULL,
        company_id    INT              NOT NULL,
        sealed_at     DATETIME2(3)     NOT NULL CONSTRAINT DF_PriSyncEventos_fec DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT PK_PriSyncEventos PRIMARY KEY CLUSTERED (scope_key, seq),
        CONSTRAINT UQ_PriSyncEventos_event UNIQUE (event_id)
    );

    CREATE NONCLUSTERED INDEX IX_PriSyncEventos_contrato
        ON dbo.PriSyncEventos (contract_id, company_id, sealed_at);
END
GO

-- Secuencia por alcance, avanzada de forma atómica por el SP.
IF OBJECT_ID(N'dbo.PriSyncSecuencias', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncSecuencias
    (
        scope_key  VARCHAR(64) NOT NULL,
        last_seq   BIGINT      NOT NULL CONSTRAINT DF_PriSyncSecuencias_seq DEFAULT (0),
        CONSTRAINT PK_PriSyncSecuencias PRIMARY KEY CLUSTERED (scope_key)
    );
END
GO

-- Registro de cajas: la configuración del servidor manda sobre lo que
-- declare el cliente en el handshake.
IF OBJECT_ID(N'dbo.PriSyncCajas', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncCajas
    (
        contract_id        INT          NOT NULL,
        company_id         INT          NOT NULL,
        branch_id          INT          NOT NULL,
        pos_id             INT          NOT NULL,
        role               VARCHAR(20)  NOT NULL CONSTRAINT DF_PriSyncCajas_rol DEFAULT ('caja'),
        db_mode            VARCHAR(20)  NOT NULL CONSTRAINT DF_PriSyncCajas_modo DEFAULT ('per-pos'),
        enabled            BIT          NOT NULL CONSTRAINT DF_PriSyncCajas_hab DEFAULT (1),
        agent_version      VARCHAR(20)  NULL,
        last_connected_at  DATETIME2(3) NULL,
        CONSTRAINT PK_PriSyncCajas PRIMARY KEY CLUSTERED (contract_id, company_id, branch_id, pos_id)
    );
END
GO

-- Banderazos: hasta qué secuencia aplicó cada caja, por alcance.
IF OBJECT_ID(N'dbo.PriSyncCajasAck', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncCajasAck
    (
        contract_id       INT          NOT NULL,
        company_id        INT          NOT NULL,
        branch_id         INT          NOT NULL,
        pos_id            INT          NOT NULL,
        scope_key         VARCHAR(64)  NOT NULL,
        last_applied_seq  BIGINT       NOT NULL,
        actualizado       DATETIME2(3) NOT NULL CONSTRAINT DF_PriSyncCajasAck_fec DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT PK_PriSyncCajasAck PRIMARY KEY CLUSTERED (contract_id, company_id, branch_id, pos_id, scope_key)
    );
END
GO

-- Permisos de publicación caja -> master (fila más específica gana).
IF OBJECT_ID(N'dbo.PriSyncPermisos', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncPermisos
    (
        contract_id  INT         NOT NULL,
        company_id   INT         NULL,
        branch_id    INT         NULL,
        pos_id       INT         NULL,
        entity_type  VARCHAR(32) NOT NULL,
        can_publish  BIT         NOT NULL,
        id           INT IDENTITY(1,1) NOT NULL,
        CONSTRAINT PK_PriSyncPermisos PRIMARY KEY CLUSTERED (id)
    );
END
GO

-- Conflictos y rechazos auditados.
IF OBJECT_ID(N'dbo.PriSyncConflictos', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.PriSyncConflictos
    (
        id                      BIGINT IDENTITY(1,1) NOT NULL,
        event_id                UNIQUEIDENTIFIER     NOT NULL,
        entity_type             VARCHAR(32)          NOT NULL,
        entity_key              VARCHAR(64)          NULL,
        origin_json             NVARCHAR(400)        NOT NULL,
        discarded_payload_json  NVARCHAR(MAX)        NULL,
        master_payload_json     NVARCHAR(MAX)        NULL,
        reason                  VARCHAR(40)          NOT NULL,
        created_at              DATETIME2(3)         NOT NULL CONSTRAINT DF_PriSyncConf_fec DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT PK_PriSyncConflictos PRIMARY KEY CLUSTERED (id)
    );
END
GO

-- ------------------------------ PROCEDIMIENTOS -------------------------------

-- Avanza y retorna la siguiente secuencia del alcance, de forma atómica.
IF OBJECT_ID(N'dbo.sp_ApiSync_Secuencia_Next', N'P') IS NOT NULL DROP PROCEDURE dbo.sp_ApiSync_Secuencia_Next;
GO
CREATE PROCEDURE dbo.sp_ApiSync_Secuencia_Next
    @scope_key VARCHAR(64)
AS
BEGIN
    SET NOCOUNT ON;

    -- Upsert atómico: crea el alcance en 0 si no existe y avanza en 1.
    MERGE dbo.PriSyncSecuencias WITH (HOLDLOCK) AS destino
    USING (SELECT @scope_key AS scope_key) AS origen
        ON destino.scope_key = origen.scope_key
    WHEN MATCHED THEN UPDATE SET last_seq = destino.last_seq + 1
    WHEN NOT MATCHED THEN INSERT (scope_key, last_seq) VALUES (@scope_key, 1);

    SELECT last_seq FROM dbo.PriSyncSecuencias WHERE scope_key = @scope_key;
END
GO

-- Lee la configuración registrada de una caja.
IF OBJECT_ID(N'dbo.sp_ApiSync_Caja_Read', N'P') IS NOT NULL DROP PROCEDURE dbo.sp_ApiSync_Caja_Read;
GO
CREATE PROCEDURE dbo.sp_ApiSync_Caja_Read
    @contract_id INT, @company_id INT, @branch_id INT, @pos_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT contract_id, company_id, branch_id, pos_id, role, db_mode, enabled
      FROM dbo.PriSyncCajas
     WHERE contract_id = @contract_id AND company_id = @company_id
       AND branch_id = @branch_id AND pos_id = @pos_id;
END
GO

-- Registra el banderazo (última secuencia aplicada) de una caja. Nunca
-- retrocede: un ack atrasado no pisa uno más nuevo.
IF OBJECT_ID(N'dbo.sp_ApiSync_Caja_RegistrarAck', N'P') IS NOT NULL DROP PROCEDURE dbo.sp_ApiSync_Caja_RegistrarAck;
GO
CREATE PROCEDURE dbo.sp_ApiSync_Caja_RegistrarAck
    @contract_id INT, @company_id INT, @branch_id INT, @pos_id INT,
    @scope_key VARCHAR(64), @applied_seq BIGINT
AS
BEGIN
    SET NOCOUNT ON;

    MERGE dbo.PriSyncCajasAck WITH (HOLDLOCK) AS destino
    USING (SELECT @contract_id AS c, @company_id AS e, @branch_id AS s, @pos_id AS p, @scope_key AS k) AS origen
        ON destino.contract_id = origen.c AND destino.company_id = origen.e
       AND destino.branch_id = origen.s AND destino.pos_id = origen.p
       AND destino.scope_key = origen.k
    WHEN MATCHED AND destino.last_applied_seq < @applied_seq
        THEN UPDATE SET last_applied_seq = @applied_seq, actualizado = SYSUTCDATETIME()
    WHEN NOT MATCHED
        THEN INSERT (contract_id, company_id, branch_id, pos_id, scope_key, last_applied_seq)
             VALUES (@contract_id, @company_id, @branch_id, @pos_id, @scope_key, @applied_seq);
END
GO

-- Sella un evento en la cola oficial. Falla con error real si el
-- event_id ya existe (idempotencia: el llamador trata el duplicado).
IF OBJECT_ID(N'dbo.sp_ApiSync_Evento_Create', N'P') IS NOT NULL DROP PROCEDURE dbo.sp_ApiSync_Evento_Create;
GO
CREATE PROCEDURE dbo.sp_ApiSync_Evento_Create
    @scope_key VARCHAR(64), @seq BIGINT, @event_id UNIQUEIDENTIFIER,
    @entity_type VARCHAR(32), @entity_key VARCHAR(64) = NULL,
    @operation VARCHAR(10), @origin_json NVARCHAR(400),
    @payload_json NVARCHAR(MAX), @payload_hash CHAR(71),
    @contract_id INT, @company_id INT
AS
BEGIN
    SET NOCOUNT ON;
    -- Sello idempotente: si el evento ya fue sellado en una pasada previa
    -- (p.ej. el "marcar publicado" del outbox fallo de forma transitoria y
    -- el poller reprocesa la fila), no se reintenta el INSERT para no chocar
    -- con UQ_PriSyncEventos_event y quedar en un bucle. La UQ sigue como
    -- resguardo de idempotencia extremo a extremo.
    IF NOT EXISTS (SELECT 1 FROM dbo.PriSyncEventos WHERE event_id = @event_id)
        INSERT INTO dbo.PriSyncEventos
            (scope_key, seq, event_id, entity_type, entity_key, operation,
             origin_json, payload_json, payload_hash, contract_id, company_id)
        VALUES
            (@scope_key, @seq, @event_id, @entity_type, @entity_key, @operation,
             @origin_json, @payload_json, @payload_hash, @contract_id, @company_id);
END
GO

-- ------------------------- SEED AMBIENTE DESARROLLO --------------------------
-- Caja de prueba: contrato 1000, empresa 1 (SOLTECS / PRIXUS_76377910).
IF NOT EXISTS (SELECT 1 FROM dbo.PriSyncCajas
                WHERE contract_id = 1000 AND company_id = 1 AND branch_id = 1 AND pos_id = 1)
    INSERT INTO dbo.PriSyncCajas (contract_id, company_id, branch_id, pos_id, role, db_mode, enabled)
    VALUES (1000, 1, 1, 1, 'caja', 'per-pos', 1);
GO

-- =============================================================================
-- ROLLBACK (solo para revertir todo):
-- USE master; DROP DATABASE IF EXISTS PrixusSyncHub;
-- =============================================================================
