create table alumnos (
  id_alumno bigint primary key generated always as identity,
  nombre text not null,
  apellido1 text not null,
  apellido2 text,
  ID_legal text not null,
  fecha_nacimiento date not null,
  code_expediente text not null
);

create table ciclos_formativos (
  id_ciclo bigint primary key generated always as identity,
  nombre_ciclo text not null,
  nivel text not null check (nivel in ('Grado Medio', 'Grado Superior')),
  "regulación" text
);

create table modulos (
  id_modulo bigint primary key generated always as identity,
  "código_modulo" text not null,
  nombre_modulo text not null,
  ciclo_asignado bigint references ciclos_formativos (id_ciclo),
  "duración" int not null
);

create table calificaciones (
  "id_calificación" bigint primary key generated always as identity,
  id_alumno bigint references alumnos (id_alumno),
  id_modulo bigint references modulos (id_modulo),
  "calificación" numeric(3, 2) not null,
  convocatoria int not null,
  curso_escolar text not null,
  estado_modulo text not null check (
    estado_modulo in (
      'Superado',
      'No Superado',
      'Exento',
      'Convalidado'
    )
  )
);

create table certificados (
  id_certificado bigint primary key generated always as identity,
  id_alumno bigint references alumnos (id_alumno),
  "fecha_expedición" date not null,
  tipo_certificado text not null,
  resultado_final text,
  "estado_título" text not null check (
    "estado_título" in ('Cumple Requisitos', 'No Cumple Requisitos')
  ),
  observaciones text
);

create table firmantes (
  id_firmante bigint primary key generated always as identity,
  nombre_firmante text not null,
  cargo text not null
);

alter table calificaciones
alter column id_alumno
set not null;

alter table calificaciones
alter column id_modulo
set not null;

alter table certificados
alter column id_alumno
set not null;

alter table certificados
rename column resultado_final to nota_media;

truncate calificaciones;

alter table calificaciones
add column ano_escolar text not null;

create table regulaciones (
  id_regulacion bigint primary key generated always as identity,
  ano_escolar text not null,
  id_ciclo bigint references ciclos_formativos (id_ciclo),
  real_decreto text not null,
  orden text not null
);

create table certificado_regulaciones (
  id_certificado_regulacion bigint primary key generated always as identity,
  id_certificado bigint references certificados (id_certificado),
  id_regulacion bigint references regulaciones (id_regulacion)
);

drop table if exists certificado_regulaciones;

drop table if exists regulaciones;

create table regulaciones (
  id_regulacion bigint primary key generated always as identity,
  id_ciclo bigint references ciclos_formativos (id_ciclo),
  ano_escolar text not null,
  real_decreto text not null,
  orden text not null
);

alter table modulos
alter column ciclo_asignado
set not null;

alter table certificados
add column requisito_academico boolean not null default false;

alter table certificados
alter column "fecha_expedición"
drop not null;

alter table certificados
alter column "fecha_expedición"
drop not null;

comment on column ciclos_formativos.nivel is 'Grado Medio o Grado Superior';

alter table ciclos_formativos
drop "regulación";

alter table regulaciones
add column norma_legal text;

alter table regulaciones
drop real_decreto;

alter table regulaciones
drop orden;

alter table regulaciones
alter column norma_legal
set not null;

alter table regulaciones
alter column id_ciclo
set not null;

alter table alumnos
rename column dni to id_legal;