-- SCRIPT FISICO (DDL)
--
-- Data Criacao ...........: 30/09/2025
-- Autor(es) ..............: Paulo Henrique Virgilio Cerqueira
-- Banco de Dados .........: postgres:13-alpine
--
-- PROJETO => 01 Base de Dados
-- => 03 Tabelas
-- -------------------------------------------------------------------------------------
CREATE TABLE video (
idVideo SERIAL NOT NULL,
hash CHAR(64) NOT NULL UNIQUE,
dataUpload TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT pk_video PRIMARY KEY (idVideo)
);
CREATE TABLE audio (
idAudio SERIAL NOT NULL,
idVideo INT UNIQUE NOT NULL, -- 1:1 com video
tempo INTERVAL,
formato VARCHAR(10),
hash CHAR(64) NOT NULL UNIQUE,
CONSTRAINT pk_audio PRIMARY KEY (idAudio),
CONSTRAINT fk_audio_video FOREIGN KEY (idVideo) REFERENCES video(idVideo)
ON DELETE CASCADE
);
CREATE TABLE transcricao (
idTranscricao SERIAL NOT NULL,
idAudio INT UNIQUE NOT NULL, -- 1:1 com audio
textoTranscricao TEXT NOT NULL,
dataProcessamento TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
sumario VARCHAR(255),
CONSTRAINT pk_transcricao PRIMARY KEY (idTranscricao),
CONSTRAINT fk_transcricao_audio FOREIGN KEY (idAudio) REFERENCES audio(idAudio)
ON DELETE CASCADE
);
