Compare commits
| Author | SHA1 | Date | |
|---|---|---|---|
|
|
d84cc7e13e |
@@ -0,0 +1,87 @@
|
||||
import {
|
||||
Column,
|
||||
CreateDateColumn,
|
||||
Entity,
|
||||
JoinColumn,
|
||||
ManyToOne,
|
||||
PrimaryGeneratedColumn,
|
||||
UpdateDateColumn,
|
||||
} from 'typeorm';
|
||||
import { Users } from './users.entity';
|
||||
|
||||
/** Aligné sur type_evenement_agenda_type (évite import circulaire avec EvenementsAgenda). */
|
||||
export enum AgendaLotTypeType {
|
||||
ABSENCE_ENFANT = 'absence_enfant',
|
||||
CONGE_AM = 'conge_am',
|
||||
ARRET_MALADIE_AM = 'arret_maladie_am',
|
||||
SOIN_ENFANT = 'soin_enfant',
|
||||
}
|
||||
|
||||
export enum AgendaLotStatutType {
|
||||
EN_ATTENTE = 'en_attente',
|
||||
ACCEPTE = 'accepte',
|
||||
REFUSE = 'refuse',
|
||||
ANNULE = 'annule',
|
||||
}
|
||||
|
||||
/**
|
||||
* Lot collectif Agenda (congé / arrêt / absence groupés) — cycle versionné.
|
||||
* Ticket #214 (B0).
|
||||
*/
|
||||
@Entity('agenda_lots', { schema: 'public' })
|
||||
export class AgendaLots {
|
||||
@PrimaryGeneratedColumn('uuid')
|
||||
id: string;
|
||||
|
||||
@Column({
|
||||
type: 'enum',
|
||||
enum: AgendaLotTypeType,
|
||||
enumName: 'type_evenement_agenda_type',
|
||||
name: 'type',
|
||||
})
|
||||
type: AgendaLotTypeType;
|
||||
|
||||
@Column({
|
||||
type: 'enum',
|
||||
enum: AgendaLotStatutType,
|
||||
enumName: 'agenda_lot_statut_type',
|
||||
name: 'statut',
|
||||
default: AgendaLotStatutType.EN_ATTENTE,
|
||||
})
|
||||
statut: AgendaLotStatutType;
|
||||
|
||||
@Column({ name: 'revision', type: 'int', default: 1 })
|
||||
revision: number;
|
||||
|
||||
@Column({ name: 'soumis_le', type: 'timestamptz', default: () => 'now()' })
|
||||
soumis_le: Date;
|
||||
|
||||
@Column({ name: 'verrouille_le', type: 'timestamptz', nullable: true })
|
||||
verrouille_le?: Date;
|
||||
|
||||
@Column({ name: 'refuse_le', type: 'timestamptz', nullable: true })
|
||||
refuse_le?: Date;
|
||||
|
||||
@Column({ name: 'annule_le', type: 'timestamptz', nullable: true })
|
||||
annule_le?: Date;
|
||||
|
||||
@Column({ name: 'annule_par', type: 'uuid', nullable: true })
|
||||
annule_par?: string;
|
||||
|
||||
@ManyToOne(() => Users, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'annule_par', referencedColumnName: 'id' })
|
||||
annulePar?: Users;
|
||||
|
||||
@Column({ name: 'cree_par', type: 'uuid', nullable: true })
|
||||
cree_par?: string;
|
||||
|
||||
@ManyToOne(() => Users, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'cree_par', referencedColumnName: 'id' })
|
||||
createdBy?: Users;
|
||||
|
||||
@CreateDateColumn({ name: 'cree_le', type: 'timestamptz' })
|
||||
cree_le: Date;
|
||||
|
||||
@UpdateDateColumn({ name: 'modifie_le', type: 'timestamptz' })
|
||||
modifie_le: Date;
|
||||
}
|
||||
@@ -14,16 +14,19 @@ import { Users } from './users.entity';
|
||||
import { CardType } from './card_types.entity';
|
||||
import { CardAudienceMember } from './card_audience_members.entity';
|
||||
import { CardResponse } from './card_responses.entity';
|
||||
import { AgendaLots } from './agenda_lots.entity';
|
||||
|
||||
export enum CardInstanceStatutType {
|
||||
OUVERTE = 'ouverte',
|
||||
REFUSEE = 'refusee',
|
||||
TRAITEE = 'traitee',
|
||||
ANNULEE = 'annulee',
|
||||
}
|
||||
|
||||
export enum CardOperationType {
|
||||
CREATE = 'create',
|
||||
UPDATE = 'update',
|
||||
CANCEL = 'cancel',
|
||||
}
|
||||
|
||||
@Entity('card_instances', { schema: 'public' })
|
||||
@@ -83,6 +86,27 @@ export class CardInstance {
|
||||
@Column({ name: 'purge_at', type: 'timestamptz' })
|
||||
purge_at: Date;
|
||||
|
||||
@Column({ name: 'id_lot', type: 'uuid', nullable: true })
|
||||
id_lot?: string;
|
||||
|
||||
@ManyToOne(() => AgendaLots, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'id_lot', referencedColumnName: 'id' })
|
||||
lot?: AgendaLots;
|
||||
|
||||
@Column({ name: 'annule_le', type: 'timestamptz', nullable: true })
|
||||
annule_le?: Date;
|
||||
|
||||
@Column({ name: 'annule_par', type: 'uuid', nullable: true })
|
||||
annule_par?: string;
|
||||
|
||||
@ManyToOne(() => Users, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'annule_par', referencedColumnName: 'id' })
|
||||
annulePar?: Users;
|
||||
|
||||
/** Réanimation dans le fil (tri COALESCE(remonte_le, cree_le)). */
|
||||
@Column({ name: 'remonte_le', type: 'timestamptz', nullable: true })
|
||||
remonte_le?: Date;
|
||||
|
||||
@OneToMany(() => CardAudienceMember, (m) => m.card)
|
||||
audience: CardAudienceMember[];
|
||||
|
||||
|
||||
@@ -47,6 +47,10 @@ export class CardResponse {
|
||||
@Column({ name: 'comment', type: 'text', nullable: true })
|
||||
comment?: string;
|
||||
|
||||
/** Doit matcher agenda_lots.revision pour compter dans l'unanimité (#214). */
|
||||
@Column({ name: 'revision_lot', type: 'int', default: 1 })
|
||||
revision_lot: number;
|
||||
|
||||
@CreateDateColumn({ name: 'cree_le', type: 'timestamptz' })
|
||||
cree_le: Date;
|
||||
}
|
||||
|
||||
@@ -9,6 +9,7 @@ import {
|
||||
} from 'typeorm';
|
||||
import { AmChildren } from './am_children.entity';
|
||||
import { Users } from './users.entity';
|
||||
import { AgendaLots } from './agenda_lots.entity';
|
||||
|
||||
export enum TypeEvenementAgendaType {
|
||||
ABSENCE_ENFANT = 'absence_enfant',
|
||||
@@ -21,12 +22,13 @@ export enum StatutEvenementAgendaType {
|
||||
EN_ATTENTE = 'en_attente',
|
||||
ACCEPTE = 'accepte',
|
||||
REFUSE = 'refuse',
|
||||
ANNULE = 'annule',
|
||||
}
|
||||
|
||||
/**
|
||||
* Vérité métier agenda par couple AM↔enfant (placement).
|
||||
* Types V1 : absences / congés / arrêts ; extensible (soins, école, mairie…).
|
||||
* Les cartes collectent ; cette table historise. Ticket #205.
|
||||
* Les cartes collectent ; cette table historise. Tickets #205 / #214.
|
||||
*/
|
||||
@Entity('evenements_agenda', { schema: 'public' })
|
||||
export class EvenementsAgenda {
|
||||
@@ -74,13 +76,40 @@ export class EvenementsAgenda {
|
||||
@JoinColumn({ name: 'cree_par', referencedColumnName: 'id' })
|
||||
createdBy?: Users;
|
||||
|
||||
/** Réf. carte de collecte (FK quand module Cartes existera). */
|
||||
/** Réf. carte de collecte (module Cartes). */
|
||||
@Column({ name: 'id_card_instance', type: 'uuid', nullable: true })
|
||||
id_card_instance?: string;
|
||||
|
||||
@Column({ name: 'motif', type: 'text', nullable: true })
|
||||
motif?: string;
|
||||
|
||||
/** Heure métier (soin) — durable hors payload carte. */
|
||||
@Column({ name: 'heure', type: 'time', nullable: true })
|
||||
heure?: string;
|
||||
|
||||
@Column({ name: 'id_lot', type: 'uuid', nullable: true })
|
||||
id_lot?: string;
|
||||
|
||||
@ManyToOne(() => AgendaLots, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'id_lot', referencedColumnName: 'id' })
|
||||
lot?: AgendaLots;
|
||||
|
||||
@Column({ name: 'verrouille_le', type: 'timestamptz', nullable: true })
|
||||
verrouille_le?: Date;
|
||||
|
||||
@Column({ name: 'refuse_le', type: 'timestamptz', nullable: true })
|
||||
refuse_le?: Date;
|
||||
|
||||
@Column({ name: 'annule_le', type: 'timestamptz', nullable: true })
|
||||
annule_le?: Date;
|
||||
|
||||
@Column({ name: 'annule_par', type: 'uuid', nullable: true })
|
||||
annule_par?: string;
|
||||
|
||||
@ManyToOne(() => Users, { nullable: true, onDelete: 'SET NULL' })
|
||||
@JoinColumn({ name: 'annule_par', referencedColumnName: 'id' })
|
||||
annulePar?: Users;
|
||||
|
||||
@CreateDateColumn({ name: 'cree_le', type: 'timestamptz' })
|
||||
cree_le: Date;
|
||||
|
||||
|
||||
@@ -177,6 +177,10 @@ export class Users {
|
||||
@Column({ nullable: true, name: 'numero_dossier', length: 20 })
|
||||
numero_dossier?: string;
|
||||
|
||||
/** Préférences UI (ex. jours_affiches_semaine) — #214 */
|
||||
@Column({ name: 'preferences', type: 'jsonb', default: {} })
|
||||
preferences: Record<string, unknown>;
|
||||
|
||||
@ManyToOne(() => Relais, relais => relais.gestionnaires, { nullable: true })
|
||||
@JoinColumn({ name: 'relais_id' })
|
||||
relais?: Relais;
|
||||
|
||||
+43
-3
@@ -42,7 +42,12 @@ DO $$ BEGIN
|
||||
END IF;
|
||||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_evenement_agenda_type') THEN
|
||||
CREATE TYPE statut_evenement_agenda_type AS ENUM (
|
||||
'en_attente', 'accepte', 'refuse'
|
||||
'en_attente', 'accepte', 'refuse', 'annule'
|
||||
);
|
||||
END IF;
|
||||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'agenda_lot_statut_type') THEN
|
||||
CREATE TYPE agenda_lot_statut_type AS ENUM (
|
||||
'en_attente', 'accepte', 'refuse', 'annule'
|
||||
);
|
||||
END IF;
|
||||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_validation_type') THEN
|
||||
@@ -109,7 +114,8 @@ CREATE TABLE utilisateurs (
|
||||
ville VARCHAR(150),
|
||||
code_postal VARCHAR(10),
|
||||
profession VARCHAR(150),
|
||||
situation_familiale situation_familiale_type
|
||||
situation_familiale situation_familiale_type,
|
||||
preferences JSONB NOT NULL DEFAULT '{}'::jsonb
|
||||
);
|
||||
|
||||
CREATE INDEX idx_utilisateurs_token_creation_mdp
|
||||
@@ -321,9 +327,31 @@ CREATE TABLE avenants_contrats (
|
||||
modifie_le TIMESTAMPTZ DEFAULT now()
|
||||
);
|
||||
|
||||
-- ==========================================================
|
||||
-- Table : agenda_lots (cycles collectifs versionnés — #214)
|
||||
-- ==========================================================
|
||||
CREATE TABLE agenda_lots (
|
||||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||||
type type_evenement_agenda_type NOT NULL,
|
||||
statut agenda_lot_statut_type NOT NULL DEFAULT 'en_attente',
|
||||
revision INT NOT NULL DEFAULT 1,
|
||||
soumis_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
verrouille_le TIMESTAMPTZ,
|
||||
refuse_le TIMESTAMPTZ,
|
||||
annule_le TIMESTAMPTZ,
|
||||
annule_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
cree_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
cree_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
modifie_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
CONSTRAINT chk_agenda_lots_revision CHECK (revision >= 1)
|
||||
);
|
||||
|
||||
CREATE INDEX idx_agenda_lots_type_statut ON agenda_lots (type, statut);
|
||||
CREATE INDEX idx_agenda_lots_soumis_le ON agenda_lots (soumis_le);
|
||||
|
||||
-- ==========================================================
|
||||
-- Table : evenements_agenda (vérité métier agenda (absences/congés/arrêt + futurs types))
|
||||
-- 1 ligne = 1 période ; rattachement = placement AM↔enfant (#193)
|
||||
-- 1 ligne = 1 période ; rattachement = placement AM↔enfant (#193 / #214)
|
||||
-- ==========================================================
|
||||
CREATE TABLE evenements_agenda (
|
||||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||||
@@ -337,6 +365,12 @@ CREATE TABLE evenements_agenda (
|
||||
cree_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
id_card_instance UUID,
|
||||
motif TEXT,
|
||||
heure TIME,
|
||||
id_lot UUID REFERENCES agenda_lots(id) ON DELETE SET NULL,
|
||||
verrouille_le TIMESTAMPTZ,
|
||||
refuse_le TIMESTAMPTZ,
|
||||
annule_le TIMESTAMPTZ,
|
||||
annule_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
cree_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
modifie_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
CONSTRAINT chk_evenements_agenda_dates CHECK (date_fin >= date_debut)
|
||||
@@ -349,6 +383,12 @@ CREATE INDEX idx_evenements_agenda_placement_type_statut
|
||||
CREATE INDEX idx_evenements_agenda_expire_at
|
||||
ON evenements_agenda (expire_at)
|
||||
WHERE statut IN ('en_attente', 'refuse');
|
||||
CREATE INDEX idx_evenements_agenda_id_lot
|
||||
ON evenements_agenda (id_lot)
|
||||
WHERE id_lot IS NOT NULL;
|
||||
CREATE INDEX idx_evenements_agenda_placement_dates_actif
|
||||
ON evenements_agenda (id_placement, date_debut, date_fin)
|
||||
WHERE statut IS DISTINCT FROM 'annule';
|
||||
|
||||
-- ==========================================================
|
||||
-- Table : signalements_bugs
|
||||
|
||||
+17
-2
@@ -119,7 +119,7 @@ Ce document recense **toutes les valeurs énumérées** utilisées dans la base
|
||||
|
||||
---
|
||||
|
||||
## 8) Statut d’absence de garde — `statut`
|
||||
## 8) Statut d’événement agenda — `statut`
|
||||
|
||||
**Tables/colonnes** : `evenements_agenda.statut`
|
||||
**Valeurs autorisées** :
|
||||
@@ -128,7 +128,22 @@ Ce document recense **toutes les valeurs énumérées** utilisées dans la base
|
||||
|---|---|
|
||||
| `en_attente` | En attente de validation / ack |
|
||||
| `accepte` | Accepté / acté (conservé en historique métier) |
|
||||
| `refuse` | Refusé (temporaire ; purge TTL) |
|
||||
| `refuse` | Refusé (temporaire ; visible barré puis soft-cancel) |
|
||||
| `annule` | Annulation douce (#214) — hors vues normales, auditable |
|
||||
|
||||
---
|
||||
|
||||
## 8bis) Statut de lot agenda — `agenda_lots.statut`
|
||||
|
||||
**Enum** : `agenda_lot_statut_type`
|
||||
**Valeurs** : `en_attente` · `accepte` · `refuse` · `annule` (issue globale du lot versionné, #214)
|
||||
|
||||
---
|
||||
|
||||
## 8ter) Cartes — soft-cancel / opération
|
||||
|
||||
**`card_instance_statut_type`** : `ouverte` · `refusee` · `traitee` · `annulee` (#214)
|
||||
**`card_operation_type`** : `create` · `update` · `cancel` (#214)
|
||||
|
||||
---
|
||||
|
||||
|
||||
@@ -0,0 +1,174 @@
|
||||
-- Ticket #214 — Agenda B0 : lots versionnés, soft-cancel, prefs, remonte_le
|
||||
-- Migration base live. Idempotent.
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 1. Enums
|
||||
-- ---------------------------------------------------------------------------
|
||||
DO $$
|
||||
BEGIN
|
||||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'agenda_lot_statut_type') THEN
|
||||
CREATE TYPE agenda_lot_statut_type AS ENUM (
|
||||
'en_attente', 'accepte', 'refuse', 'annule'
|
||||
);
|
||||
END IF;
|
||||
END $$;
|
||||
|
||||
-- PG : ADD VALUE IF NOT EXISTS (hors sous-transaction fragile)
|
||||
ALTER TYPE statut_evenement_agenda_type ADD VALUE IF NOT EXISTS 'annule';
|
||||
ALTER TYPE card_instance_statut_type ADD VALUE IF NOT EXISTS 'annulee';
|
||||
ALTER TYPE card_operation_type ADD VALUE IF NOT EXISTS 'cancel';
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 2. Table agenda_lots
|
||||
-- ---------------------------------------------------------------------------
|
||||
CREATE TABLE IF NOT EXISTS agenda_lots (
|
||||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||||
type type_evenement_agenda_type NOT NULL,
|
||||
statut agenda_lot_statut_type NOT NULL DEFAULT 'en_attente',
|
||||
revision INT NOT NULL DEFAULT 1,
|
||||
soumis_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
verrouille_le TIMESTAMPTZ,
|
||||
refuse_le TIMESTAMPTZ,
|
||||
annule_le TIMESTAMPTZ,
|
||||
annule_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
cree_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
|
||||
cree_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
modifie_le TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||||
CONSTRAINT chk_agenda_lots_revision CHECK (revision >= 1)
|
||||
);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_agenda_lots_type_statut
|
||||
ON agenda_lots (type, statut);
|
||||
CREATE INDEX IF NOT EXISTS idx_agenda_lots_soumis_le
|
||||
ON agenda_lots (soumis_le);
|
||||
|
||||
COMMENT ON TABLE agenda_lots IS
|
||||
'Lots collectifs Agenda (congé/arrêt/absence groupés) — cycle versionné (#214).';
|
||||
COMMENT ON COLUMN agenda_lots.revision IS
|
||||
'Incrémenté à chaque resoumission ; les card_responses.revision_lot doivent correspondre.';
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 3. evenements_agenda — soft-cancel + lot + heure
|
||||
-- ---------------------------------------------------------------------------
|
||||
ALTER TABLE evenements_agenda
|
||||
ADD COLUMN IF NOT EXISTS annule_le TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS annule_par UUID,
|
||||
ADD COLUMN IF NOT EXISTS verrouille_le TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS refuse_le TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS heure TIME,
|
||||
ADD COLUMN IF NOT EXISTS id_lot UUID;
|
||||
|
||||
DO $$
|
||||
BEGIN
|
||||
IF NOT EXISTS (
|
||||
SELECT 1 FROM pg_constraint WHERE conname = 'evenements_agenda_annule_par_fkey'
|
||||
) THEN
|
||||
ALTER TABLE evenements_agenda
|
||||
ADD CONSTRAINT evenements_agenda_annule_par_fkey
|
||||
FOREIGN KEY (annule_par) REFERENCES utilisateurs(id) ON DELETE SET NULL;
|
||||
END IF;
|
||||
IF NOT EXISTS (
|
||||
SELECT 1 FROM pg_constraint WHERE conname = 'evenements_agenda_id_lot_fkey'
|
||||
) THEN
|
||||
ALTER TABLE evenements_agenda
|
||||
ADD CONSTRAINT evenements_agenda_id_lot_fkey
|
||||
FOREIGN KEY (id_lot) REFERENCES agenda_lots(id) ON DELETE SET NULL;
|
||||
END IF;
|
||||
END $$;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_evenements_agenda_id_lot
|
||||
ON evenements_agenda (id_lot)
|
||||
WHERE id_lot IS NOT NULL;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_evenements_agenda_verrouille_le
|
||||
ON evenements_agenda (verrouille_le)
|
||||
WHERE verrouille_le IS NOT NULL;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_evenements_agenda_refuse_le
|
||||
ON evenements_agenda (refuse_le)
|
||||
WHERE refuse_le IS NOT NULL;
|
||||
|
||||
COMMENT ON COLUMN evenements_agenda.heure IS
|
||||
'Heure métier (soin) — durable hors payload carte purgeable.';
|
||||
COMMENT ON COLUMN evenements_agenda.verrouille_le IS
|
||||
'Écrit à la soumission/resoumission — jamais pendant un GET.';
|
||||
COMMENT ON COLUMN evenements_agenda.id_lot IS
|
||||
'Lot collectif optionnel (unanimité congé / envoi groupé arrêt-absence).';
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 4. card_instances — soft-cancel + id_lot + remonte_le
|
||||
-- ---------------------------------------------------------------------------
|
||||
ALTER TABLE card_instances
|
||||
ADD COLUMN IF NOT EXISTS id_lot UUID,
|
||||
ADD COLUMN IF NOT EXISTS annule_le TIMESTAMPTZ,
|
||||
ADD COLUMN IF NOT EXISTS annule_par UUID,
|
||||
ADD COLUMN IF NOT EXISTS remonte_le TIMESTAMPTZ;
|
||||
|
||||
DO $$
|
||||
BEGIN
|
||||
IF NOT EXISTS (
|
||||
SELECT 1 FROM pg_constraint WHERE conname = 'card_instances_id_lot_fkey'
|
||||
) THEN
|
||||
ALTER TABLE card_instances
|
||||
ADD CONSTRAINT card_instances_id_lot_fkey
|
||||
FOREIGN KEY (id_lot) REFERENCES agenda_lots(id) ON DELETE SET NULL;
|
||||
END IF;
|
||||
IF NOT EXISTS (
|
||||
SELECT 1 FROM pg_constraint WHERE conname = 'card_instances_annule_par_fkey'
|
||||
) THEN
|
||||
ALTER TABLE card_instances
|
||||
ADD CONSTRAINT card_instances_annule_par_fkey
|
||||
FOREIGN KEY (annule_par) REFERENCES utilisateurs(id) ON DELETE SET NULL;
|
||||
END IF;
|
||||
END $$;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_card_instances_id_lot
|
||||
ON card_instances (id_lot)
|
||||
WHERE id_lot IS NOT NULL;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_card_instances_remonte_le
|
||||
ON card_instances (remonte_le DESC NULLS LAST);
|
||||
|
||||
COMMENT ON COLUMN card_instances.remonte_le IS
|
||||
'Renseigné uniquement à la réanimation d''une carte sortie du fil ; tri fil = COALESCE(remonte_le, cree_le).';
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 5. card_responses — revision_lot
|
||||
-- ---------------------------------------------------------------------------
|
||||
ALTER TABLE card_responses
|
||||
ADD COLUMN IF NOT EXISTS revision_lot INT NOT NULL DEFAULT 1;
|
||||
|
||||
DO $$
|
||||
BEGIN
|
||||
IF NOT EXISTS (
|
||||
SELECT 1 FROM pg_constraint WHERE conname = 'chk_card_responses_revision_lot'
|
||||
) THEN
|
||||
ALTER TABLE card_responses
|
||||
ADD CONSTRAINT chk_card_responses_revision_lot CHECK (revision_lot >= 1);
|
||||
END IF;
|
||||
END $$;
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_card_responses_card_revision
|
||||
ON card_responses (id_card, revision_lot);
|
||||
|
||||
COMMENT ON COLUMN card_responses.revision_lot IS
|
||||
'Doit matcher agenda_lots.revision courante pour compter dans l''unanimité.';
|
||||
|
||||
-- Backfill : réponses existantes = revision 1 (déjà DEFAULT)
|
||||
UPDATE card_responses SET revision_lot = 1 WHERE revision_lot IS NULL;
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 6. utilisateurs.preferences
|
||||
-- ---------------------------------------------------------------------------
|
||||
ALTER TABLE utilisateurs
|
||||
ADD COLUMN IF NOT EXISTS preferences JSONB NOT NULL DEFAULT '{}'::jsonb;
|
||||
|
||||
COMMENT ON COLUMN utilisateurs.preferences IS
|
||||
'Préférences UI (ex. jours_affiches_semaine). PATCH fusionne les clés.';
|
||||
|
||||
-- ---------------------------------------------------------------------------
|
||||
-- 7. Index lecture Agenda (statut hors annule côté app ; index utile quand même)
|
||||
-- ---------------------------------------------------------------------------
|
||||
CREATE INDEX IF NOT EXISTS idx_evenements_agenda_placement_dates_actif
|
||||
ON evenements_agenda (id_placement, date_debut, date_fin)
|
||||
WHERE statut IS DISTINCT FROM 'annule';
|
||||
@@ -79,12 +79,42 @@ LEFT JOIN avenants_contrats a ON a.id_contrat = c.id
|
||||
GROUP BY c.id, c.id_dossier, c.statut
|
||||
ORDER BY c.cree_le DESC;
|
||||
|
||||
\echo '=== 9) Absences_garde (30 derniers jours) ====================='
|
||||
SELECT ag.id, ag.type, ag.id_placement, ag.date_debut, ag.date_fin, ag.statut, ag.expire_at
|
||||
\echo '=== 9) Événements agenda (30 derniers jours) ==================='
|
||||
SELECT ag.id, ag.type, ag.id_placement, ag.date_debut, ag.date_fin, ag.statut,
|
||||
ag.id_lot, ag.heure, ag.verrouille_le, ag.expire_at
|
||||
FROM evenements_agenda ag
|
||||
WHERE ag.date_debut >= (NOW()::date - INTERVAL '30 days')
|
||||
ORDER BY ag.date_debut DESC;
|
||||
|
||||
\echo '=== 9b) Agenda B0 (#214) — schéma lots / soft-cancel / prefs ==='
|
||||
SELECT COUNT(*) AS nb_lots FROM agenda_lots;
|
||||
SELECT column_name
|
||||
FROM information_schema.columns
|
||||
WHERE table_name = 'evenements_agenda'
|
||||
AND column_name IN ('id_lot', 'heure', 'verrouille_le', 'refuse_le', 'annule_le', 'annule_par')
|
||||
ORDER BY column_name;
|
||||
SELECT column_name
|
||||
FROM information_schema.columns
|
||||
WHERE table_name = 'card_instances'
|
||||
AND column_name IN ('id_lot', 'annule_le', 'annule_par', 'remonte_le')
|
||||
ORDER BY column_name;
|
||||
SELECT column_name
|
||||
FROM information_schema.columns
|
||||
WHERE table_name = 'card_responses' AND column_name = 'revision_lot';
|
||||
SELECT column_name
|
||||
FROM information_schema.columns
|
||||
WHERE table_name = 'utilisateurs' AND column_name = 'preferences';
|
||||
SELECT enumlabel
|
||||
FROM pg_enum e
|
||||
JOIN pg_type t ON t.oid = e.enumtypid
|
||||
WHERE t.typname IN (
|
||||
'statut_evenement_agenda_type',
|
||||
'agenda_lot_statut_type',
|
||||
'card_instance_statut_type',
|
||||
'card_operation_type'
|
||||
)
|
||||
ORDER BY t.typname, e.enumsortorder;
|
||||
|
||||
\echo '=== 10) Uploads & notifications récentes ======================='
|
||||
SELECT u.courriel, up.fichier_url, up.type_fichier, up.cree_le
|
||||
FROM uploads up
|
||||
|
||||
Reference in New Issue
Block a user