-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';

-- Tabla: Provincia
CREATE TABLE Provincia (
    codigo_provincia INT PRIMARY KEY, -- Código único de provincia
    nombre VARCHAR(100) NOT NULL -- Nombre de la provincia
);

-- Tabla: Canton
CREATE TABLE Canton (
    codigo_canton INT PRIMARY KEY, -- Código único de cantón
    nombre VARCHAR(100) NOT NULL, -- Nombre del cantón
    codigo_provincia INT NOT NULL, -- Relación con provincia
    FOREIGN KEY (codigo_provincia) REFERENCES Provincia(codigo_provincia)
);

-- Tabla: Parroquia
CREATE TABLE Parroquia (
    codigo_parroquia INT PRIMARY KEY, -- Código único de parroquia
    nombre VARCHAR(100) NOT NULL, -- Nombre de la parroquia
    codigo_canton INT NOT NULL, -- Relación con cantón
    FOREIGN KEY (codigo_canton) REFERENCES Canton(codigo_canton)
);

-- Tabla: Ciudadano
CREATE TABLE Ciudadano (
    cedula VARCHAR(20) PRIMARY KEY, -- Clave primaria única para ciudadanos
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    fecha_nacimiento DATE,
    direccion TEXT,
    telefono VARCHAR(15)
);

-- Tabla: TipoUsuario
CREATE TABLE TipoUsuario (
    tipo_usuario_id SERIAL PRIMARY KEY, -- Clave primaria de tipo de usuario
    nombre VARCHAR(50) NOT NULL UNIQUE -- Ejemplo: "Administrador", "Responsable de Organización"
);

-- Tabla: Usuario
CREATE TABLE Usuario (
    usuario_id SERIAL PRIMARY KEY, -- Clave primaria para usuarios del sistema
    correo VARCHAR(100) NOT NULL UNIQUE,
    contraseña VARCHAR(100) NOT NULL,
    tipo_usuario_id INT NOT NULL, -- Relación con tipo de usuario
    FOREIGN KEY (tipo_usuario_id) REFERENCES TipoUsuario(tipo_usuario_id)
);


-- Tabla: Organizacion
CREATE TABLE Organizacion (
    organizacion_id SERIAL PRIMARY KEY, -- Identificador único de organización
    nombre VARCHAR(100) NOT NULL, -- Nombre de la organización
    tipo_organizacion VARCHAR(50) NOT NULL, -- Nacional, Provincial, Cantonal, Parroquial
    codigo_provincia INT DEFAULT NULL, -- Código de provincia
    codigo_canton INT DEFAULT NULL, -- Código de cantón
    codigo_parroquia INT DEFAULT NULL, -- Código de parroquia
    responsable_cedula VARCHAR(20) NOT NULL, -- Cedula del responsable (ciudadano)
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Fecha de creación
    FOREIGN KEY (responsable_cedula) REFERENCES Ciudadano(cedula),
    FOREIGN KEY (codigo_provincia) REFERENCES Provincia(codigo_provincia),
    FOREIGN KEY (codigo_canton) REFERENCES Canton(codigo_canton),
    FOREIGN KEY (codigo_parroquia) REFERENCES Parroquia(codigo_parroquia)
);

-- Tabla: Blockchain
CREATE TABLE Blockchain (
    tx_id SERIAL PRIMARY KEY, -- Identificador único de la transacción
    hash_transaccion VARCHAR(255) NOT NULL UNIQUE, -- Hash de la transacción en la blockchain
    tipo_transaccion VARCHAR(50) NOT NULL, -- Ejemplo: "Registro de afiliación", "Creación de organización"
    data JSONB NOT NULL, -- Información asociada a la transacción (en formato JSON)
    fecha_transaccion TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Fecha de registro en blockchain
);

-- Tabla: Afiliacion
CREATE TABLE Afiliacion (
    afiliacion_id SERIAL PRIMARY KEY, -- Identificador único de afiliación
    cedula VARCHAR(20) NOT NULL, -- Relación con ciudadano
    organizacion_id INT NOT NULL, -- Relación con organización
    fecha_afiliacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Fecha de la afiliación
    estado VARCHAR(50) NOT NULL, -- Activa, Cancelada, etc.
    blockchain_tx_id INT, -- Relación con la tabla Blockchain (trazabilidad)
    UNIQUE (cedula), -- Un ciudadano solo puede estar afiliado a una organización a la vez
    FOREIGN KEY (cedula) REFERENCES Ciudadano(cedula),
    FOREIGN KEY (organizacion_id) REFERENCES Organizacion(organizacion_id),
    FOREIGN KEY (blockchain_tx_id) REFERENCES Blockchain(tx_id)
);



-- Relación de Blockchain con Organizacion (opcional, si es necesario registrar transacciones específicas)
CREATE TABLE BlockchainOrganizacion (
    id SERIAL PRIMARY KEY,
    organizacion_id INT NOT NULL, -- Relación con organización
    blockchain_tx_id INT NOT NULL, -- Relación con Blockchain
    FOREIGN KEY (organizacion_id) REFERENCES Organizacion(organizacion_id),
    FOREIGN KEY (blockchain_tx_id) REFERENCES Blockchain(tx_id)
);

-- Tabla: SSI (Self-Sovereign Identity)
CREATE TABLE SSI (
    ssi_id SERIAL PRIMARY KEY, -- Identificador único de la credencial SSI
    cedula VARCHAR(20) NOT NULL, -- Relación con el ciudadano
    credencial_digital JSONB NOT NULL, -- Información de la credencial en formato JSON
    estado VARCHAR(50) NOT NULL, -- Activa, Revocada, etc.
    fecha_emision TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Fecha de emisión de la credencial
    fecha_revocacion TIMESTAMP DEFAULT NULL, -- Fecha de revocación (si aplica)
    FOREIGN KEY (cedula) REFERENCES Ciudadano(cedula)
);

-- Tabla: Auditoria (Trazabilidad)
CREATE TABLE Auditoria (
    auditoria_id SERIAL PRIMARY KEY, -- Identificador único de auditoría
    accion VARCHAR(255) NOT NULL, -- Acción realizada (Ejemplo: "Afiliación", "Revocación")
    usuario_id INT NOT NULL, -- Usuario que realizó la acción
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Fecha de la acción
    descripcion TEXT, -- Descripción de la acción
    FOREIGN KEY (usuario_id) REFERENCES Usuario(usuario_id)
);
