CREATE TABLE Personal (
    PersonalID INT PRIMARY KEY,
    Nume VARCHAR(50) NOT NULL,
    Prenume VARCHAR(50) NOT NULL,
    DataNasterii DATE NOT NULL,
    LocNastere VARCHAR(50) NOT NULL,
    JudNastere VARCHAR(50) NOT NULL,
    CNP VARCHAR(13) UNIQUE NOT NULL,
    Adresa VARCHAR(100) NOT NULL,
    Localitate VARCHAR(50) NOT NULL,
    Judet VARCHAR(50) NOT NULL,
    Telefon VARCHAR(15) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    CONSTRAINT chk_email CHECK (Email LIKE '%@%.%'),
    CONSTRAINT chk_cnp CHECK (LEN(CNP) = 13)
);

CREATE TABLE Functii (
    FunctieID INT PRIMARY KEY,
    PersonalID INT NOT NULL,
    ParohieID INT NOT NULL,
    Functia VARCHAR(50) NOT NULL,
    BeginData DATE NOT NULL,
    EndData DATE,
    Observatii TEXT,
    FOREIGN KEY (PersonalID) REFERENCES Personal(PersonalID),
    FOREIGN KEY (ParohieID) REFERENCES Parohie(ParohieID),
    CONSTRAINT chk_date CHECK (BeginData <= EndData OR EndData IS NULL)
);

CREATE TABLE Activitati (
    ActivitateID INT PRIMARY KEY,
    PersonalID INT NOT NULL,
    NumeActivitate VARCHAR(100) NOT NULL,
    Descriere TEXT,
    DataActivitate DATE,
    FOREIGN KEY (PersonalID) REFERENCES Personal(PersonalID)
);

CREATE TABLE Cladiri (
    CladireID INT PRIMARY KEY,
    ParohieID INT NOT NULL,
    TipCladire VARCHAR(50) NOT NULL,
    AnConstructie INT,
    Suprafata DECIMAL(10,2),
    StareCladire VARCHAR(50),
    FOREIGN KEY (ParohieID) REFERENCES Parohie(ParohieID)
);

CREATE TABLE Inventar (
    InventarID INT PRIMARY KEY,
    UnitCultCentrala VARCHAR(50) DEFAULT 'ARB' NOT NULL,
    ParohieID INT NOT NULL,
    Localitate VARCHAR(100) NOT NULL,
    Denumire VARCHAR(200) NOT NULL,
    Valoare DECIMAL(10,2),
    NrInventar VARCHAR(4) UNIQUE NOT NULL,
    DataIntrarii DATE NOT NULL,
    AutorProducator VARCHAR(100),
    Descriere TEXT,
    UnitateMasura VARCHAR(20),
    NrUnitateMasura DECIMAL(10,2),
    Colectia VARCHAR(100),
    MaterialTitlu VARCHAR(200),
    DimensiuniGreutate VARCHAR(100),
    StareConservare VARCHAR(50),
    Provenienta VARCHAR(200),
    DocumentIntrare VARCHAR(100),
    ValoareIntrare DECIMAL(10,2),
    DataIesire DATE,
    MotivIesire TEXT,
    DataUltimaVerificare DATE,
    Observatii TEXT,
    CategorieBun VARCHAR(50) NOT NULL,
    FOREIGN KEY (ParohieID) REFERENCES Parohie(ParohieID),
    CONSTRAINT chk_nr_inventar CHECK (
        NrInventar LIKE '[A-F][0-9][0-9][0-9]'
    ),
    CONSTRAINT chk_valoare_intrare CHECK (ValoareIntrare >= 0),
    CONSTRAINT chk_valoare CHECK (Valoare >= 0),
    CONSTRAINT chk_data_iesire CHECK (DataIesire IS NULL OR DataIesire >= DataIntrarii)
);

CREATE TABLE Evenimente (
    EventID INT PRIMARY KEY,
    ParohieID INT NOT NULL,
    NumeEveniment VARCHAR(100) NOT NULL,
    TipEveniment VARCHAR(50) NOT NULL,
    DataStart DATETIME NOT NULL,
    DataFinal DATETIME,
    Locatie VARCHAR(100),
    Descriere TEXT,
    ResponsabilID INT,
    StatusEveniment VARCHAR(20) DEFAULT 'Planificat',
    NrParticipanti INT,
    Buget DECIMAL(10,2),
    FOREIGN KEY (ParohieID) REFERENCES Parohie(ParohieID),
    FOREIGN KEY (ResponsabilID) REFERENCES Personal(PersonalID),
    CONSTRAINT chk_event_dates CHECK (DataStart <= DataFinal OR DataFinal IS NULL),
    CONSTRAINT chk_status CHECK (StatusEveniment IN ('Planificat', 'In Desfasurare', 'Finalizat', 'Anulat')),
    CONSTRAINT chk_participanti CHECK (NrParticipanti >= 0),
    CONSTRAINT chk_buget CHECK (Buget >= 0)
); 