

-- Table des alternants
CREATE TABLE apprentices (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  birth_day TEXT,
  birth_month TEXT,
  birth_year TEXT,
  street_number TEXT,
  street TEXT,
  postal_code TEXT,
  city TEXT,
  education TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Table des entreprises
CREATE TABLE companies (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  siret TEXT,
  street_number TEXT,
  street TEXT,
  postal_code TEXT,
  city TEXT,
  email TEXT,
  phone TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Table des maîtres d'apprentissage
CREATE TABLE mentors (
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  birth_day TEXT,
  birth_month TEXT,
  birth_year TEXT,
  position TEXT,
  email TEXT NOT NULL,
  phone TEXT,
  experience TEXT,
  company_id INTEGER,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
);

-- Table des dossiers d'alternance
CREATE TABLE files (
  id SERIAL PRIMARY KEY,
  apprentice_id INTEGER NOT NULL,
  company_id INTEGER NOT NULL,
  mentor_id INTEGER NOT NULL,
  stage TEXT NOT NULL DEFAULT 'REQUEST',
  start_date TEXT,
  end_date TEXT,
  duration TEXT,
  salary TEXT,
  work_hours TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (apprentice_id) REFERENCES apprentices(id) ON DELETE CASCADE,
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  FOREIGN KEY (mentor_id) REFERENCES mentors(id) ON DELETE CASCADE
);

-- Table des documents associés aux dossiers
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  file_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  path TEXT NOT NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT NOW(),
  extracted_data TEXT,
  FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE
);

-- Table des commentaires sur les dossiers
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  file_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  text TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table des activités pour le journal d'actions
CREATE TABLE activities (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  file_id INTEGER,
  activity_type TEXT NOT NULL,
  description TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE SET NULL
);

-- Table pour gérer les sessions d'authentification
CREATE TABLE sessions (
  sid TEXT PRIMARY KEY,
  sess JSONB NOT NULL,
  expire TIMESTAMP(6) NOT NULL
);

-- Création d'index pour optimiser les performances
CREATE INDEX idx_documents_file_id ON documents(file_id);
CREATE INDEX idx_comments_file_id ON comments(file_id);
CREATE INDEX idx_activities_user_id ON activities(user_id);
CREATE INDEX idx_activities_file_id ON activities(file_id);
CREATE INDEX idx_mentors_company_id ON mentors(company_id);
CREATE INDEX idx_files_apprentice_id ON files(apprentice_id);
CREATE INDEX idx_files_company_id ON files(company_id);
CREATE INDEX idx_files_mentor_id ON files(mentor_id);
CREATE INDEX idx_files_stage ON files(stage);
CREATE INDEX idx_sessions_expire ON sessions(expire);

-- IMPORTANT: Pour des raisons de sécurité, changez le mot de passe administrateur après la première connexion

-- Fonction pour mettre à jour le timestamp updated_at automatiquement
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW(); 
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Déclencheurs pour mettre à jour automatiquement updated_at
CREATE TRIGGER update_files_timestamp
BEFORE UPDATE ON files
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
COMMENT ON TABLE apprentices IS 'Alternants suivis par l''établissement';
COMMENT ON TABLE companies IS 'Entreprises partenaires accueillant des alternants';
COMMENT ON TABLE mentors IS 'Maîtres d''apprentissage encadrant les alternants';
COMMENT ON TABLE files IS 'Dossiers d''alternance avec les informations du contrat';
COMMENT ON TABLE documents IS 'Documents justificatifs liés aux dossiers';
COMMENT ON TABLE comments IS 'Commentaires des utilisateurs sur les dossiers';
COMMENT ON TABLE activities IS 'Journal des activités des utilisateurs';
COMMENT ON TABLE sessions IS 'Sessions utilisateur pour l''authentification';