GazuNET

CREATE TABLE Users (
    id SERIAL NOT NULL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50),
    password VARCHAR(50),
    phoneNumber VARCHAR(14) PRIMARY KEY NOT NULL
);

CREATE UNIQUE INDEX phoneNumber ON Users(phoneNumber);

CREATE TABLE GeoInfo(
    id SERIAL NOT NULL PRIMARY KEY,
    city VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL,
    place_residency VARCHAR(50) NOT NULL
);

CREATE TABLE Profiles (
    id SERIAL NOT NULL PRIMARY KEY,
    geoInfo_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    user_id INT,
    user_phoneNumber VARCHAR(14),
    birth_date DATE,
    project_status text,
    argument_status text,
    balance INT,
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (geoinfo_id) REFERENCES GeoInfo(id),
    FOREIGN KEY (project_status) REFERENCES project_req(project_status),
    FOREIGN KEY (argument_status)  REFERENCES conclusion_of_arg(argument_status),
    CHECK ( project_status IN ('success', 'wait', 'not success') ),
    CHECK ( argument_status IN ('success', 'wait', 'not success') )
);


CREATE INDEX profiles_fn ON Profiles(first_name);
CREATE INDEX profiles_ln ON Profiles(last_name);

CREATE TABLE TY_request (
    geoInfo_id INT,
    user_id INT,
    TIME TIME,
    profiles_status text,
    FOREIGN KEY (geoInfo_id) REFERENCES GeoInfo(id),
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

CREATE TABLE project_req (
    topography_ph varbit,
    plan varbit,
    chimney varbit,
    verification varbit,
    project_status text PRIMARY KEY
);

CREATE TABLE conclusion_of_arg (
    document varbit,
    svidet_o_vlad_doma varbit,
    pasport varbit,
    dogovor varbit,
    cotel INT NOT NULL PRIMARY KEY,
    garantiya TIME PRIMARY KEY,
    argument_status text PRIMARY KEY

);

-- либо хранение документов в своей бд, заливать в личном кабинете и ссылаться на них

CREATE TABLE Agency (
    sotr_id INT PRIMARY KEY
);

CREATE TABLE Archive (
    conclucsion_of_arg_cotel INT NOT NULL,
    conclusion_of_arg_garantiya TIME,
    FOREIGN KEY (conclusion_of_arg_garantiya) REFERENCES conclusion_of_arg(garantiya),
    FOREIGN KEY (conclucsion_of_arg_cotel) REFERENCES conclusion_of_arg(cotel)
);

CREATE TABLE Payments (
    price INT NOT NULL,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

DELETE FROM Archive
WHERE (SELECT conclusion_of_arg_garantiya
FROM Archive
WHERE conclusion_of_arg_garantiya = 0)

SELECT