1  PRAGMA foreign_keys = ON;
  2  PRAGMA journal_mode = WAL;
  3  PRAGMA synchronous = NORMAL;
  4  PRAGMA busy_timeout = 5000;
  5  
  6  CREATE TABLE IF NOT EXISTS roles (
  7      id INTEGER PRIMARY KEY AUTOINCREMENT,
  8      name TEXT NOT NULL UNIQUE,
  9      description TEXT,
 10      permissions TEXT,
 11      created_at TEXT DEFAULT CURRENT_TIMESTAMP
 12  );
 13  
 14  CREATE TABLE IF NOT EXISTS user_roles (
 15      user_id INTEGER NOT NULL,
 16      role_id INTEGER NOT NULL,
 17      PRIMARY KEY (user_id, role_id),
 18      FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 19      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
 20  );
 21  
 22  CREATE TABLE IF NOT EXISTS adherents (
 23      id INTEGER PRIMARY KEY AUTOINCREMENT,
 24      first_name TEXT NOT NULL,
 25      last_name TEXT NOT NULL,
 26      email TEXT,
 27      phone TEXT,
 28      address TEXT,
 29      city TEXT,
 30      postal_code TEXT,
 31      age INTEGER,
 32      profession TEXT,
 33      joined_at TEXT DEFAULT CURRENT_TIMESTAMP,
 34      is_active INTEGER DEFAULT 1,
 35      notes TEXT
 36  );
 37  
 38  CREATE TABLE IF NOT EXISTS users (
 39      id INTEGER PRIMARY KEY AUTOINCREMENT,
 40      username TEXT NOT NULL UNIQUE,
 41      email TEXT UNIQUE,
 42      password_hash TEXT NOT NULL,
 43      adherent_id INTEGER,
 44      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
 45      last_login TEXT,
 46      FOREIGN KEY (adherent_id) REFERENCES adherents(id) ON DELETE SET NULL
 47  );
 48  
 49  
 50  
 51  CREATE TABLE IF NOT EXISTS contributions (
 52      id INTEGER PRIMARY KEY AUTOINCREMENT,
 53      adherents_id INTEGER NOT NULL,
 54      amount_cents INTEGER NOT NULL,           
 55      currency TEXT NOT NULL DEFAULT 'EUR',
 56      paid_at TEXT DEFAULT CURRENT_TIMESTAMP,
 57      method TEXT,                             
 58      reference TEXT,
 59      notes TEXT,
 60      FOREIGN KEY (adherents_id) REFERENCES adherents(id) ON DELETE CASCADE
 61  );
 62  
 63  
 64  
 65  CREATE TABLE IF NOT EXISTS missions (
 66      id INTEGER PRIMARY KEY AUTOINCREMENT,
 67      title TEXT NOT NULL,
 68      description TEXT,
 69      location TEXT,
 70      start_at INTEGER,
 71      end_at INTEGER,
 72      capacity INTEGER,                        
 73      budget_cents INTEGER DEFAULT 0,
 74      created_by INTEGER,                      
 75      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
 76      FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
 77  );
 78  
 79  
 80  
 81  CREATE TABLE IF NOT EXISTS mission_participants (
 82      id INTEGER PRIMARY KEY AUTOINCREMENT,
 83      mission_id INTEGER NOT NULL,
 84      adherent_id INTEGER NOT NULL,
 85      role TEXT,                               
 86      status TEXT DEFAULT 'registered',        
 87      registered_at TEXT DEFAULT CURRENT_TIMESTAMP,
 88      FOREIGN KEY (mission_id) REFERENCES missions(id) ON DELETE CASCADE,
 89      FOREIGN KEY (adherent_id) REFERENCES adherents(id) ON DELETE CASCADE,
 90      UNIQUE(mission_id, adherent_id)
 91  );
 92  
 93  
 94  
 95  CREATE TABLE IF NOT EXISTS documents (
 96      id INTEGER PRIMARY KEY AUTOINCREMENT,
 97      filename TEXT NOT NULL,                  
 98      original_name TEXT,                      
 99      mime_type TEXT,
100      size_bytes INTEGER,
101      uploader_id INTEGER,                     
102      uploaded_at TEXT DEFAULT CURRENT_TIMESTAMP,
103      path TEXT NOT NULL,                      
104      description TEXT,
105      FOREIGN KEY (uploader_id) REFERENCES users(id) ON DELETE SET NULL
106  );
107  
108  
109  
110  CREATE TABLE IF NOT EXISTS mission_documents (
111      mission_id INTEGER NOT NULL,
112      document_id INTEGER NOT NULL,
113      PRIMARY KEY (mission_id, document_id),
114      FOREIGN KEY (mission_id) REFERENCES missions(id) ON DELETE CASCADE,
115      FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
116  );
117  
118  
119  CREATE TABLE IF NOT EXISTS articles (
120      id INTEGER PRIMARY KEY AUTOINCREMENT,
121      title TEXT NOT NULL,
122      content TEXT,
123      author_id INTEGER,
124      status TEXT DEFAULT 'draft',             
125      published_at INTEGER,
126      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
127      updated_at INTEGER,
128      FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
129  );
130  
131  
132  
133  CREATE TABLE IF NOT EXISTS article_media (
134      article_id INTEGER NOT NULL,
135      document_id INTEGER NOT NULL,
136      PRIMARY KEY (article_id, document_id),
137      FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
138      FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
139  );
140  
141  
142  CREATE TABLE IF NOT EXISTS partners (
143      id INTEGER PRIMARY KEY AUTOINCREMENT,
144      name TEXT NOT NULL,
145      contact TEXT,
146      email TEXT UNIQUE,
147      phone TEXT,
148      address TEXT,
149      website TEXT,
150      notes TEXT,
151      added_at TEXT DEFAULT CURRENT_TIMESTAMP
152  );
153  
154  
155  CREATE TABLE IF NOT EXISTS subsidies (
156      id INTEGER PRIMARY KEY AUTOINCREMENT,
157      partner_id INTEGER,                      
158      title TEXT NOT NULL,
159      amount_cents INTEGER NOT NULL,
160      currency TEXT NOT NULL DEFAULT 'EUR',
161      awarded_at INTEGER,
162      conditions TEXT,
163      notes TEXT,
164      FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE SET NULL
165  );
166  
167  
168  
169  CREATE TABLE IF NOT EXISTS donors (
170      id INTEGER PRIMARY KEY AUTOINCREMENT,
171      name TEXT NOT NULL,
172      contact TEXT,
173      email TEXT,
174      notes TEXT,
175      added_at TEXT DEFAULT CURRENT_TIMESTAMP
176  );
177  
178  CREATE TABLE IF NOT EXISTS donations (
179      id INTEGER PRIMARY KEY AUTOINCREMENT,
180      donor_id INTEGER NOT NULL,
181      amount_cents INTEGER NOT NULL,
182      currency TEXT NOT NULL DEFAULT 'EUR',
183      donated_at TEXT DEFAULT CURRENT_TIMESTAMP,
184      method TEXT,
185      reference TEXT,
186      notes TEXT,
187      FOREIGN KEY (donor_id) REFERENCES donors(id) ON DELETE CASCADE
188  );
189  
190  
191  
192  CREATE TABLE IF NOT EXISTS audit_logs (
193      id INTEGER PRIMARY KEY AUTOINCREMENT,
194      entity TEXT,                              
195      entity_id INTEGER,
196      action TEXT,                              
197      performed_by INTEGER,                      
198      details TEXT,                              
199      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
200      FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL
201  );
202  
203  CREATE TABLE IF NOT EXISTS sessions (
204      session_id TEXT PRIMARY KEY,
205      user_id INTEGER NOT NULL,
206      created_at TEXT DEFAULT CURRENT_TIMESTAMP,
207      expires_at TEXT NOT NULL,
208      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
209  );
210  
211  INSERT INTO roles (name, description, permissions) VALUES
212  ('admin', 'Administrateur du site avec tous les droits', 'all'),
213  ('responsable-benevoles', 'Responsable du pôle Bénévoles', 'benevoles'),
214  ('responsable-communication', 'Responsable du pôle Communication', 'communication'),
215  ('responsable-partenariats', 'Responsable du pôle Partenariats', 'partenariats'),
216  ('responsable-tresorerie', 'Responsable du pôle Trésorerie', 'tresorerie'),
217  ('responsable-direction', 'Responsable du pôle Direction', 'direction');