SET lock_timeout = '10s';
SET statement_timeout = '120000';

CREATE SCHEMA IF NOT EXISTS commerce;
CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS commerce.planes_pago_eventos_globales (
    id_evento_global integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    business_id varchar(100) NOT NULL,
    sucursal integer NOT NULL,
    nombre varchar(160) NOT NULL,
    descripcion text NULL,
    fecha_evento date NOT NULL,
    activo boolean NOT NULL DEFAULT true,
    orden integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by_login_id integer NULL,
    updated_at timestamptz NOT NULL DEFAULT now(),
    updated_by_login_id integer NULL,
    soft_delete boolean NOT NULL DEFAULT false,
    soft_delete_date timestamptz NULL,
    CONSTRAINT chk_planes_pago_eventos_globales_sucursal_positive
        CHECK (sucursal >= 1),
    CONSTRAINT chk_planes_pago_eventos_globales_orden_non_negative
        CHECK (orden >= 0),
    CONSTRAINT chk_planes_pago_eventos_globales_nombre_not_blank
        CHECK (length(btrim(nombre)) > 0)
);

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_globales_nombre_fecha_unq_idx
    ON commerce.planes_pago_eventos_globales USING btree (
        business_id,
        sucursal,
        fecha_evento,
        lower(nombre)
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS planes_pago_eventos_globales_business_sucursal_fecha_idx
    ON commerce.planes_pago_eventos_globales USING btree (
        business_id,
        sucursal,
        fecha_evento
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS planes_pago_eventos_globales_activo_idx
    ON commerce.planes_pago_eventos_globales USING btree (
        business_id,
        sucursal,
        activo,
        orden,
        fecha_evento
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE TABLE IF NOT EXISTS commerce.planes_pago_eventos_globales_mesas (
    id_evento_mesa bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id_evento_global integer NOT NULL,
    business_id varchar(100) NOT NULL,
    numero_mesa integer NOT NULL,
    cantidad_sillas integer NOT NULL,
    cantidad_lugares integer NOT NULL,
    orden integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by_login_id integer NULL,
    updated_at timestamptz NOT NULL DEFAULT now(),
    updated_by_login_id integer NULL,
    soft_delete boolean NOT NULL DEFAULT false,
    soft_delete_date timestamptz NULL,
    CONSTRAINT fk_planes_pago_eventos_globales_mesas_evento
        FOREIGN KEY (id_evento_global)
        REFERENCES commerce.planes_pago_eventos_globales (id_evento_global)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_planes_pago_eventos_globales_mesas_numero_positive
        CHECK (numero_mesa >= 1),
    CONSTRAINT chk_planes_pago_eventos_globales_mesas_cantidad_sillas_positive
        CHECK (cantidad_sillas >= 1),
    CONSTRAINT chk_planes_pago_eventos_globales_mesas_cantidad_lugares_positive
        CHECK (cantidad_lugares >= 1),
    CONSTRAINT chk_planes_pago_eventos_globales_mesas_orden_non_negative
        CHECK (orden >= 0)
);

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_globales_mesas_numero_unq_idx
    ON commerce.planes_pago_eventos_globales_mesas USING btree (
        id_evento_global,
        numero_mesa
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS planes_pago_eventos_globales_mesas_evento_orden_idx
    ON commerce.planes_pago_eventos_globales_mesas USING btree (
        business_id,
        id_evento_global,
        orden,
        numero_mesa
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS planes_pago_eventos_globales_mesas_business_idx
    ON commerce.planes_pago_eventos_globales_mesas USING btree (
        business_id,
        id_evento_global
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE TABLE IF NOT EXISTS commerce.planes_pago_eventos_participantes (
    id_participante_evento bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    business_id varchar(100) NOT NULL,
    id_evento_global integer NOT NULL,
    id_plan integer NOT NULL,
    fecha_vinculacion timestamptz NOT NULL DEFAULT now(),
    fecha_liquidacion_referencia timestamptz NULL,
    referencia_liquidacion_source varchar(64) NULL,
    folio_actual integer NULL,
    folio_total_evento integer NULL,
    lugar_global integer NULL,
    id_evento_mesa_asignada bigint NULL,
    numero_mesa_asignada integer NULL,
    lugar_en_mesa integer NULL,
    estado_asignacion varchar(40) NOT NULL DEFAULT 'pendiente_liquidacion',
    ultimo_recalculo_at timestamptz NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    created_by_login_id integer NULL,
    updated_at timestamptz NOT NULL DEFAULT now(),
    updated_by_login_id integer NULL,
    soft_delete boolean NOT NULL DEFAULT false,
    soft_delete_date timestamptz NULL,
    CONSTRAINT fk_planes_pago_eventos_participantes_evento
        FOREIGN KEY (id_evento_global)
        REFERENCES commerce.planes_pago_eventos_globales (id_evento_global)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_planes_pago_eventos_participantes_plan
        FOREIGN KEY (id_plan)
        REFERENCES commerce.planes_pago (id_plan)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_planes_pago_eventos_participantes_mesa
        FOREIGN KEY (id_evento_mesa_asignada)
        REFERENCES commerce.planes_pago_eventos_globales_mesas (id_evento_mesa)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT chk_planes_pago_eventos_participantes_folio_positive
        CHECK (folio_actual IS NULL OR folio_actual >= 1),
    CONSTRAINT chk_planes_pago_eventos_participantes_total_positive
        CHECK (folio_total_evento IS NULL OR folio_total_evento >= 1),
    CONSTRAINT chk_planes_pago_eventos_participantes_lugar_global_positive
        CHECK (lugar_global IS NULL OR lugar_global >= 1),
    CONSTRAINT chk_planes_pago_eventos_participantes_numero_mesa_positive
        CHECK (numero_mesa_asignada IS NULL OR numero_mesa_asignada >= 1),
    CONSTRAINT chk_planes_pago_eventos_participantes_lugar_mesa_positive
        CHECK (lugar_en_mesa IS NULL OR lugar_en_mesa >= 1),
    CONSTRAINT chk_planes_pago_eventos_participantes_estado_asignacion
        CHECK (
            estado_asignacion IN (
                'pendiente_liquidacion',
                'asignado',
                'overflow_capacidad',
                'desvinculado'
            )
        ),
    CONSTRAINT chk_planes_pago_eventos_participantes_referencia_source
        CHECK (
            referencia_liquidacion_source IS NULL
            OR referencia_liquidacion_source IN (
                'planes_pago.fecha_liquidacion',
                'pagos_planes.max_fecha_pago'
            )
        ),
    CONSTRAINT chk_planes_pago_eventos_participantes_folio_le_total
        CHECK (
            folio_actual IS NULL
            OR folio_total_evento IS NULL
            OR folio_actual <= folio_total_evento
        ),
    CONSTRAINT chk_planes_pago_eventos_participantes_mesa_slot_coherence
        CHECK (
            (
                id_evento_mesa_asignada IS NULL
                AND numero_mesa_asignada IS NULL
                AND lugar_en_mesa IS NULL
            )
            OR (
                id_evento_mesa_asignada IS NOT NULL
                AND numero_mesa_asignada IS NOT NULL
                AND lugar_en_mesa IS NOT NULL
            )
        )
);

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_participantes_plan_activo_unq_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        business_id,
        id_plan
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_participantes_folio_unq_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        id_evento_global,
        folio_actual
    )
    WHERE (
        soft_delete IS NOT TRUE
        AND folio_actual IS NOT NULL
    );

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_participantes_lugar_global_unq_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        id_evento_global,
        lugar_global
    )
    WHERE (
        soft_delete IS NOT TRUE
        AND lugar_global IS NOT NULL
    );

CREATE UNIQUE INDEX IF NOT EXISTS planes_pago_eventos_participantes_mesa_lugar_unq_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        id_evento_global,
        id_evento_mesa_asignada,
        lugar_en_mesa
    )
    WHERE (
        soft_delete IS NOT TRUE
        AND id_evento_mesa_asignada IS NOT NULL
        AND lugar_en_mesa IS NOT NULL
    );

CREATE INDEX IF NOT EXISTS planes_pago_eventos_participantes_evento_orden_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        business_id,
        id_evento_global,
        fecha_liquidacion_referencia,
        id_plan
    )
    WHERE (soft_delete IS NOT TRUE);

CREATE INDEX IF NOT EXISTS planes_pago_eventos_participantes_plan_lookup_idx
    ON commerce.planes_pago_eventos_participantes USING btree (
        business_id,
        id_plan,
        id_evento_global
    );

COMMENT ON TABLE commerce.planes_pago_eventos_globales IS 'Catalogo global de eventos con fecha, folios y layout de lugares para planes de pago por negocio y sucursal.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.id_evento_global IS 'Identificador unico del evento global de folios.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.business_id IS 'Tenant del negocio propietario del evento global.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.sucursal IS 'Sucursal donde el evento global esta disponible.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.nombre IS 'Nombre visible del evento global, unico por negocio, sucursal, fecha y registro activo.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.descripcion IS 'Descripcion opcional para contexto operativo del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.fecha_evento IS 'Fecha canonica del evento global, debe coincidir con la fecha limite del modelo o plan enlazado.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.activo IS 'Permite ocultar el evento para nuevas selecciones sin borrar su historial.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.orden IS 'Orden preferido de visualizacion en catologos de eventos.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.soft_delete IS 'Borrado logico del evento global.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales.soft_delete_date IS 'Fecha de borrado logico del evento global.';

COMMENT ON TABLE commerce.planes_pago_eventos_globales_mesas IS 'Layout por mesa de cada evento global de folios y lugares.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.id_evento_mesa IS 'Identificador tecnico de la mesa del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.id_evento_global IS 'Evento global al que pertenece la mesa.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.business_id IS 'Tenant del negocio para aislamiento multiempresa.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.numero_mesa IS 'Numero visible de la mesa dentro del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.cantidad_sillas IS 'Cantidad de sillas configuradas para la mesa.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.cantidad_lugares IS 'Cantidad de lugares asignables de la mesa, puede diferir de la cantidad de sillas.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.orden IS 'Orden de despliegue del layout de mesas.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.soft_delete IS 'Borrado logico de la mesa del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_globales_mesas.soft_delete_date IS 'Fecha de borrado logico de la mesa.';

COMMENT ON TABLE commerce.planes_pago_eventos_participantes IS 'Relacion viva entre planes de pago y eventos globales con folio, lugar y metadata de recalculo.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.id_participante_evento IS 'Identificador tecnico de la participacion plan-evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.business_id IS 'Tenant del negocio para aislamiento multiempresa.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.id_evento_global IS 'Evento global al que pertenece actualmente el plan.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.id_plan IS 'Plan de pago participante dentro del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.fecha_vinculacion IS 'Fecha efectiva en la que el plan quedo ligado al evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.fecha_liquidacion_referencia IS 'Marca temporal usada para ordenar folios de forma determinista.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.referencia_liquidacion_source IS 'Fuente de la fecha de liquidacion de referencia usada por el recalculo.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.folio_actual IS 'Posicion ordinal asignada entre participantes liquidados del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.folio_total_evento IS 'Total de participantes vigentes ligados al evento al momento del ultimo recalculo.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.lugar_global IS 'Posicion absoluta del lugar dentro de la capacidad fisica total del evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.id_evento_mesa_asignada IS 'Mesa concreta del layout donde quedo asignado el participante.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.numero_mesa_asignada IS 'Numero visible de la mesa asignada.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.lugar_en_mesa IS 'Numero de lugar asignado dentro de la mesa.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.estado_asignacion IS 'Estado operativo actual: pendiente, asignado, overflow o desvinculado.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.ultimo_recalculo_at IS 'Fecha del ultimo recalculo integral que toco este participante.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.soft_delete IS 'Borrado logico de la relacion plan-evento.';
COMMENT ON COLUMN commerce.planes_pago_eventos_participantes.soft_delete_date IS 'Fecha de borrado logico de la relacion plan-evento.';

INSERT INTO ops.canonical_table_map (table_name, canonical_schema, notes)
VALUES
    (
        'planes_pago_eventos_globales',
        'commerce',
        'Catalogo global de eventos de folios y lugares para planes de pago'
    ),
    (
        'planes_pago_eventos_globales_mesas',
        'commerce',
        'Layout de mesas y lugares por evento global de planes de pago'
    ),
    (
        'planes_pago_eventos_participantes',
        'commerce',
        'Relacion viva plan-evento con folios, lugares y metadata de recalculo'
    )
ON CONFLICT (table_name) DO UPDATE
SET canonical_schema = EXCLUDED.canonical_schema,
    notes = EXCLUDED.notes;

ANALYZE commerce.planes_pago_eventos_globales;
ANALYZE commerce.planes_pago_eventos_globales_mesas;
ANALYZE commerce.planes_pago_eventos_participantes;
