File size: 4,857 Bytes
1d0150f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
85be28c
 
 
 
 
 
1d0150f
 
 
 
85be28c
1d0150f
85be28c
 
 
1d0150f
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
import os
from sqlalchemy import create_engine, text
from dotenv import load_dotenv, find_dotenv

load_dotenv(find_dotenv())
DATABASE_URL = os.getenv("DATABASE_URL")
if not DATABASE_URL:
    raise RuntimeError("DATABASE_URL manquant dans .env")

engine = create_engine(DATABASE_URL, pool_pre_ping=True)

# DDL pour créer les schemas et tables de la base de données
DDL = """
-- SCHEMAS
CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS clean;
CREATE SCHEMA IF NOT EXISTS app;

-- RAW TABLES
CREATE TABLE IF NOT EXISTS raw.employees (
  id BIGSERIAL PRIMARY KEY,
  employee_external_id INTEGER NOT NULL UNIQUE,

  age INTEGER,
  genre VARCHAR(20),
  statut_marital VARCHAR(50),
  ayant_enfants VARCHAR(10),
  niveau_education INTEGER,
  domaine_etude VARCHAR(120),
  departement VARCHAR(120),
  poste VARCHAR(120),
  distance_domicile_travail INTEGER,

  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_raw_employees_employee_external_id
  ON raw.employees(employee_external_id);

CREATE TABLE IF NOT EXISTS raw.employee_snapshots (
  id BIGSERIAL PRIMARY KEY,
  employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,

  nombre_experiences_precedentes INTEGER,
  nombre_heures_travaillees INTEGER,
  annee_experience_totale INTEGER,
  annees_dans_l_entreprise INTEGER,
  annees_dans_le_poste_actuel INTEGER,
  annees_sous_responsable_actuel INTEGER,
  niveau_hierarchique_poste INTEGER,
  revenu_mensuel INTEGER,

  augmentation_salaire_precedente VARCHAR(50),
  heures_supplementaires VARCHAR(50),

  nombre_participation_pee INTEGER,
  nb_formations_suivies INTEGER,
  nombre_employee_sous_responsabilite INTEGER,

  frequence_deplacement VARCHAR(50),
  annees_depuis_la_derniere_promotion INTEGER,

  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_raw_snapshots_employee_id_created_at
  ON raw.employee_snapshots(employee_id, created_at);

CREATE TABLE IF NOT EXISTS raw.surveys (
  id BIGSERIAL PRIMARY KEY,
  employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,

  code_sondage INTEGER,
  eval_number VARCHAR(50),

  note_evaluation_precedente INTEGER,
  note_evaluation_actuelle INTEGER,

  satisfaction_employee_environnement INTEGER,
  satisfaction_employee_nature_travail INTEGER,
  satisfaction_employee_equipe INTEGER,
  satisfaction_employee_equilibre_pro_perso INTEGER,

  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_raw_surveys_employee_id_created_at
  ON raw.surveys(employee_id, created_at);

CREATE TABLE IF NOT EXISTS raw.ground_truth (
  id BIGSERIAL PRIMARY KEY,
  employee_id BIGINT NOT NULL REFERENCES raw.employees(id) ON DELETE CASCADE,

  date_event TIMESTAMPTZ NOT NULL DEFAULT now(),
  a_quitte_l_entreprise INTEGER NOT NULL CHECK (a_quitte_l_entreprise IN (0,1)),

  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_ground_truth_employee_id_date_event
  ON raw.ground_truth(employee_id, date_event);

-- APP LOGGING TABLES
CREATE TABLE IF NOT EXISTS app.prediction_requests (
  id BIGSERIAL PRIMARY KEY,

  employee_id BIGINT REFERENCES raw.employees(id) ON DELETE SET NULL,
  snapshot_id BIGINT REFERENCES raw.employee_snapshots(id) ON DELETE SET NULL,
  survey_id BIGINT REFERENCES raw.surveys(id) ON DELETE SET NULL,

  payload_json JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_prediction_requests_created_at
  ON app.prediction_requests(created_at);

CREATE TABLE IF NOT EXISTS app.predictions (
  id BIGSERIAL PRIMARY KEY,
  request_id BIGINT NOT NULL REFERENCES app.prediction_requests(id) ON DELETE CASCADE,

  model_version VARCHAR(50) NOT NULL,
  predicted_class INTEGER NOT NULL,
  predicted_proba DOUBLE PRECISION NOT NULL,
  threshold_used DOUBLE PRECISION NOT NULL,
  latency_ms INTEGER,

  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS ix_predictions_request_id_created_at
  ON app.predictions(request_id, created_at);

CREATE INDEX IF NOT EXISTS ix_predictions_created_at
  ON app.predictions(created_at);

CREATE INDEX IF NOT EXISTS ix_predictions_model_version
  ON app.predictions(model_version);
"""

# # Fonction utilitaire pour exécuter du DDL de manière robuste
# def _exec_ddl(conn, ddl: str) -> None:
#     # robuste: exécute statement par statement
#     parts = [p.strip() for p in ddl.split(";") if p.strip()]
#     for stmt in parts:
#         conn.execute(text(stmt))

# Point d'entrée du script
def main():
    with engine.begin() as conn:
        conn.exec_driver_sql(DDL)
    print("Schemas + tables créés / vérifiés (raw / clean / app)")
    # with engine.begin() as conn:
    #     _exec_ddl(conn, DDL)
    # print("Schemas + tables créés / vérifiés (raw / clean / app)")

if __name__ == "__main__":
    main()