\connect sova_backend_local

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO sova_local;

CREATE TABLE users (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    uid INTEGER NOT NULL UNIQUE,
    email VARCHAR(180),
    roles JSONB NOT NULL DEFAULT '[]',
    region_id INTEGER NOT NULL DEFAULT 91,
    password VARCHAR(255) NOT NULL,
    birth_date DATE,
    logged_in TIMESTAMP
);

CREATE TABLE departments (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    did BIGINT NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    online_mode BOOLEAN NOT NULL DEFAULT true,
    alias VARCHAR(255) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT true,
    group_name VARCHAR(255)
);

CREATE TABLE filial (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    fid INTEGER NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    region_id INTEGER,
    site_id INTEGER,
    active BOOLEAN NOT NULL DEFAULT true,
    company VARCHAR(255),
    short_name VARCHAR(255),
    phone VARCHAR(255),
    policy TEXT,
    picture VARCHAR(255),
    email VARCHAR(255),
    origin VARCHAR(255)
);

CREATE TABLE specialist (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    preview_picture VARCHAR(255),
    active BOOLEAN NOT NULL DEFAULT true,
    display_schedule BOOLEAN NOT NULL DEFAULT true,
    dcodes VARCHAR(255),
    region_id INTEGER,
    alias VARCHAR(255) NOT NULL,
    post VARCHAR(255),
    experience VARCHAR(255),
    s_type INTEGER,
    update_at TIMESTAMP NOT NULL DEFAULT now(),
    anons TEXT,
    content TEXT,
    tags JSONB,
    highlighted_tags JSONB,
    video VARCHAR(255),
    video_vertical VARCHAR(255),
    schedule_text VARCHAR(255),
    category VARCHAR(255),
    patient_age INTEGER,
    kodoper JSONB,
    only_online_mode BOOLEAN,
    prodoctor BOOLEAN,
    prodoctor_text VARCHAR(255),
    prodoctor_link VARCHAR(255),
    degree VARCHAR(255),
    kiosk BOOLEAN NOT NULL DEFAULT false,
    filials JSONB,
    accepts_dms BOOLEAN,
    specialities JSONB
);

CREATE TABLE location (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dcode BIGINT,
    department BIGINT NOT NULL,
    filial INTEGER NOT NULL,
    online_mode BOOLEAN NOT NULL DEFAULT true,
    active BOOLEAN NOT NULL DEFAULT true,
    nearest_date DATE,
    specialist_id INTEGER REFERENCES specialist(id) ON DELETE CASCADE
);

CREATE TABLE review (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    specialist_id INTEGER REFERENCES specialist(id) ON DELETE CASCADE,
    active BOOLEAN NOT NULL DEFAULT true,
    date_create DATE NOT NULL DEFAULT CURRENT_DATE,
    message TEXT NOT NULL,
    author VARCHAR(255) NOT NULL,
    rating DOUBLE PRECISION NOT NULL DEFAULT 5,
    source VARCHAR(255),
    external_id INTEGER
);

CREATE TABLE specialist_docs (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    specialist_id INTEGER REFERENCES specialist(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    picture VARCHAR(255),
    active BOOLEAN NOT NULL DEFAULT true,
    type VARCHAR(255) NOT NULL
);

CREATE TABLE specialist_dcode_description (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dcode BIGINT NOT NULL,
    department BIGINT,
    specialist_id INTEGER NOT NULL REFERENCES specialist(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    create_at TIMESTAMP NOT NULL DEFAULT now(),
    update_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE stock (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    picture VARCHAR(255),
    anons TEXT,
    start_date TIMESTAMP NOT NULL DEFAULT now(),
    end_date TIMESTAMP NOT NULL DEFAULT now() + interval '30 days'
);

CREATE TABLE stock_specialist (
    stock_id INTEGER NOT NULL REFERENCES stock(id) ON DELETE CASCADE,
    specialist_id INTEGER NOT NULL REFERENCES specialist(id) ON DELETE CASCADE,
    PRIMARY KEY (stock_id, specialist_id)
);

CREATE TABLE price_department (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    group_id INTEGER NOT NULL,
    doct_count INTEGER DEFAULT 0,
    view_in_web BOOLEAN DEFAULT true
);

CREATE TABLE price_list (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    kodoper INTEGER,
    schname VARCHAR(255),
    specname VARCHAR(255),
    speccode INTEGER,
    price_info JSONB,
    discprice NUMERIC(10,2),
    structname VARCHAR(255),
    fname VARCHAR(255),
    filial INTEGER,
    comment TEXT,
    media_id INTEGER,
    date_update TIMESTAMP DEFAULT now(),
    group_id INTEGER,
    discpercent INTEGER
);

CREATE TABLE schedule (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dcode BIGINT,
    department BIGINT,
    filial INTEGER,
    schedident BIGINT,
    workdate DATE,
    rnum VARCHAR(255),
    rfloor VARCHAR(255),
    rbuilding VARCHAR(255),
    time JSONB,
    is_free BOOLEAN DEFAULT true,
    online_mode BOOLEAN DEFAULT false,
    query_string TEXT,
    created_at TIMESTAMP DEFAULT now(),
    interval_is_free BOOLEAN DEFAULT true,
    price_info NUMERIC(10,2)
);

CREATE TABLE record (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    specialist_id INTEGER NOT NULL,
    phone VARCHAR(255) NOT NULL,
    create_at TIMESTAMP NOT NULL DEFAULT now(),
    hash VARCHAR(255) NOT NULL,
    reserve JSONB NOT NULL DEFAULT '{}'
);

CREATE TABLE alert_sms (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    record_id INTEGER UNIQUE REFERENCES record(id) ON DELETE CASCADE,
    date_create TIMESTAMP NOT NULL DEFAULT now(),
    response TEXT NOT NULL
);

CREATE TABLE widget_form (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE widget_form_input (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    text VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    bitrix24_id VARCHAR(255) NOT NULL,
    widget_form_id INTEGER NOT NULL REFERENCES widget_form(id) ON DELETE CASCADE,
    sort INTEGER NOT NULL DEFAULT 100
);

CREATE TABLE article (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT,
    preview_picture TEXT,
    active BOOLEAN DEFAULT true,
    doctors JSONB,
    services JSONB,
    region_id INTEGER,
    alias TEXT,
    anons TEXT,
    content TEXT,
    update_at TIMESTAMP DEFAULT now()
);

CREATE TABLE disease (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    preview_picture VARCHAR(255),
    active BOOLEAN DEFAULT true,
    region_id INTEGER,
    alias VARCHAR(255),
    anons TEXT,
    update_at TIMESTAMP DEFAULT now(),
    hide_picture BOOLEAN,
    read_time TEXT,
    diseases_name TEXT,
    tags_important JSONB,
    tags JSONB,
    diseases_other_name TEXT,
    symptom TEXT,
    staff TEXT,
    link_services JSONB,
    staff_list JSONB,
    staff_post JSONB,
    staff_post_exclude JSONB,
    link_faq JSONB,
    bibliography TEXT,
    staff_check JSONB,
    content TEXT
);

CREATE TABLE medical_center (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    active BOOLEAN DEFAULT true,
    region_id INTEGER,
    alias VARCHAR(255),
    anons TEXT,
    content TEXT,
    update_at TIMESTAMP DEFAULT now(),
    kod_uslug JSONB,
    doctors JSONB,
    services JSONB,
    articles JSONB,
    txt_up JSONB,
    main_link_staff TEXT,
    contraindications JSONB,
    hide_picture INTEGER,
    indications JSONB,
    link_sale JSONB,
    plus_list JSONB,
    plus_text TEXT,
    plus_title TEXT,
    process_text TEXT,
    process_title TEXT,
    services_list JSONB,
    services_photos JSONB,
    services_title TEXT,
    sort_staff JSONB,
    training_text TEXT,
    training_text_title TEXT,
    why_text TEXT,
    why_title TEXT
);

CREATE TABLE news (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    active BOOLEAN DEFAULT true,
    region_id INTEGER,
    alias VARCHAR(255),
    anons TEXT,
    content TEXT,
    update_at TIMESTAMP DEFAULT now(),
    link_el_price TEXT,
    short_name TEXT,
    timer TEXT,
    timer_bg TEXT,
    form_order JSONB,
    link_services JSONB,
    link_staff JSONB,
    photos JSONB
);

CREATE TABLE promo (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    active BOOLEAN DEFAULT true,
    region_id INTEGER,
    alias VARCHAR(255),
    anons TEXT,
    content TEXT,
    update_at TIMESTAMP DEFAULT now(),
    clinics JSONB,
    timer TEXT,
    timer_bg TEXT,
    short_name TEXT,
    link_services JSONB,
    link_staff JSONB,
    period VARCHAR(255),
    photos JSONB
);

CREATE TABLE site_services (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    active BOOLEAN DEFAULT true,
    region_id INTEGER,
    alias VARCHAR(255),
    anons TEXT,
    content TEXT,
    update_at TIMESTAMP DEFAULT now(),
    link_videoreviews JSONB,
    preview_img TEXT,
    faq JSONB,
    part_price TEXT,
    pokazaniya TEXT,
    preparation TEXT,
    protivopokazaniya TEXT,
    hide_sign_btn JSONB,
    quiz JSONB,
    tags JSONB,
    tags_important JSONB,
    banner_img TEXT,
    banner_img_m TEXT,
    banner_img_url TEXT,
    clinics JSONB,
    download_file TEXT,
    full_width_banner TEXT,
    staff_up JSONB,
    advantages JSONB,
    hide_picture INTEGER,
    kod_uslug TEXT,
    link_price TEXT,
    photos_title TEXT,
    sale_id JSONB,
    sort_staff JSONB,
    contraindications_list TEXT,
    custom_block_text TEXT,
    custom_block_text2 TEXT,
    custom_block_title TEXT,
    custom_block_title2 TEXT,
    indications_list TEXT,
    link_articles_services JSONB,
    plus_list TEXT,
    plus_text TEXT,
    plus_title TEXT,
    prepare_title TEXT,
    process_text TEXT,
    process_title TEXT,
    services_list TEXT,
    services_photos JSONB,
    services_title TEXT,
    text_up TEXT,
    training_text TEXT,
    why_text TEXT,
    why_title TEXT,
    link_faq JSONB,
    link_services JSONB,
    link_staff JSONB,
    photos JSONB
);

CREATE TABLE web_get_docinfo (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    source_table VARCHAR(255),
    doc_name VARCHAR(255),
    doc_post VARCHAR(255),
    filial INTEGER,
    viewinweb BOOLEAN,
    depnum INTEGER,
    first_schid INTEGER,
    second_schid INTEGER,
    accepts_dms BOOLEAN,
    anons TEXT,
    content TEXT,
    updated_at TIMESTAMP DEFAULT now()
);

CREATE TABLE idoctor (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dcode INTEGER,
    name VARCHAR(255),
    department INTEGER,
    filial INTEGER,
    nearest_date DATE,
    online_mode BOOLEAN DEFAULT true,
    updated TIMESTAMP DEFAULT now()
);

INSERT INTO users (uid, email, roles, region_id, password, birth_date)
VALUES (100001, md5('local.backend@example.test'), '["ROLE_USER","ROLE_ADMIN"]', 91, '$2y$13$Y98kYF03yQjzDo0HsvvWMu..4SqB3Kgv2d8GNf870MEjJbXUJ2i.a', '1990-01-01');

INSERT INTO departments (did, name, online_mode, alias, group_name)
VALUES (10, 'Терапия', true, 'terapiya', 'Взрослые специалисты');

INSERT INTO filial (fid, name, address, region_id, site_id, company, short_name, phone, email)
VALUES
    (1, 'Сова Саратов', 'Localhost, 1', 91, 1, 'ООО Local Clinic', 'Саратов', '+70000000001', 'saratov@example.test'),
    (2, 'Сова Волгоград', 'Localhost, 2', 92, 2, 'ООО Local Clinic', 'Волгоград', '+70000000002', 'volgograd@example.test'),
    (3, 'Сова Воронеж', 'Localhost, 3', 93, 3, 'ООО Local Clinic', 'Воронеж', '+70000000003', 'voronezh@example.test'),
    (4, 'Сова Краснодар', 'Localhost, 4', 94, 4, 'ООО Local Clinic', 'Краснодар', '+70000000004', 'krasnodar@example.test');

INSERT INTO specialist (name, active, display_schedule, dcodes, region_id, alias, post, experience, s_type, anons, content, kodoper, filials, accepts_dms, specialities)
VALUES ('Иванов Иван Иванович', true, true, '101', 91, 'ivanov-ivan', 'Врач-терапевт', '2014', 1, 'Тестовый врач', 'Тестовое описание врача', '[1001]', '[1]', true, '["Терапия"]');

INSERT INTO location (dcode, department, filial, online_mode, active, nearest_date, specialist_id)
VALUES (101, 10, 1, true, true, CURRENT_DATE + interval '1 day', 1);

INSERT INTO review (specialist_id, message, author, rating, source, external_id)
VALUES (1, 'Тестовый отзыв для локальной базы', 'Пациент Local', 5, 'local', 1);

INSERT INTO specialist_docs (specialist_id, name, active, type)
VALUES (1, 'Тестовый сертификат', true, 'certificate');

INSERT INTO specialist_dcode_description (dcode, department, specialist_id, content)
VALUES (101, 10, 1, 'Локальное описание врача по dcode.');

INSERT INTO stock (name, content, anons)
VALUES ('Local акция', 'Тестовая акция только для локальной разработки', 'Local');

INSERT INTO stock_specialist (stock_id, specialist_id) VALUES (1, 1);

INSERT INTO price_department (name, group_id, doct_count, view_in_web)
VALUES ('Консультации', 100, 1, true);

INSERT INTO price_list (kodoper, schname, specname, speccode, price_info, discprice, structname, fname, filial, group_id)
VALUES (1001, 'Прием терапевта', 'Иванов Иван Иванович', 101, '{"base":1500,"discount":1200}', 1200, 'Терапия', 'Сова Local', 1, 100);

INSERT INTO schedule (dcode, department, filial, schedident, workdate, time, is_free, online_mode, price_info)
VALUES (101, 10, 1, 555001, CURRENT_DATE + interval '1 day', '[{"st":"09:00","en":"09:30"}]', true, true, 1500);

INSERT INTO record (specialist_id, phone, hash, reserve)
VALUES (1, '+70000000000', md5('+70000000000'), '{"local":true,"date":"tomorrow"}');

INSERT INTO alert_sms (record_id, response)
VALUES (1, '{"status":"local-ok"}');

INSERT INTO widget_form (name) VALUES ('Local форма');
INSERT INTO widget_form_input (text, type, bitrix24_id, widget_form_id, sort)
VALUES ('Телефон', 'phone', 'PHONE', 1, 10);

INSERT INTO article (name, active, region_id, alias, anons, content)
VALUES ('Local статья', true, 91, 'local-article', 'Анонс', 'Контент локальной статьи');

INSERT INTO disease (name, active, region_id, alias, anons, content)
VALUES ('Local заболевание', true, 91, 'local-disease', 'Анонс', 'Описание');

INSERT INTO medical_center (name, active, region_id, alias, anons, content)
VALUES ('Local медцентр', true, 91, 'local-center', 'Анонс', 'Описание');

INSERT INTO news (name, active, region_id, alias, anons, content)
VALUES ('Local новость', true, 91, 'local-news', 'Анонс', 'Контент');

INSERT INTO promo (name, active, region_id, alias, anons, content, period)
VALUES ('Local промо', true, 91, 'local-promo', 'Анонс', 'Контент', 'Всегда');

INSERT INTO site_services (name, active, region_id, alias, anons, content, tags)
VALUES ('Local услуга', true, 91, 'local-service', 'Анонс', 'Контент', '["local"]');

INSERT INTO web_get_docinfo (source_table, doc_name, doc_post, filial, viewinweb, depnum, accepts_dms, anons, content)
VALUES ('local', 'Иванов Иван Иванович', 'Врач-терапевт', 1, true, 10, true, 'Анонс', 'Описание');

INSERT INTO idoctor (dcode, name, department, filial, nearest_date, online_mode)
VALUES (101, 'Иванов Иван Иванович', 10, 1, CURRENT_DATE + interval '1 day', true);

-- После seed INSERT sequence identity должен указывать на MAX(id), иначе CREATE вернёт duplicate key id=1.
DO $body$
DECLARE
    tbl text;
BEGIN
    FOREACH tbl IN ARRAY ARRAY[
        'users', 'departments', 'filial', 'specialist', 'location', 'review',
        'specialist_docs', 'specialist_dcode_description', 'stock',
        'price_department', 'price_list', 'schedule', 'record', 'alert_sms',
        'widget_form', 'widget_form_input', 'article', 'disease', 'medical_center',
        'news', 'promo', 'site_services', 'web_get_docinfo', 'idoctor'
    ]
    LOOP
        EXECUTE format(
            'SELECT setval(pg_get_serial_sequence(%L, ''id''), COALESCE((SELECT MAX(id) FROM %I), 1), true)',
            tbl,
            tbl
        );
    END LOOP;
END
$body$;