initdb.sql

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
-- Необхідні розширення
CREATE DATABASE geo_db;
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS postgis;

-- ENUM типи
CREATE TYPE verification_status_enum AS ENUM ('unverified', 'pending', 'verified');
CREATE TYPE organization_type_enum AS ENUM ('government', 'business', 'ngo');
CREATE TYPE location_type_enum AS ENUM ('government_building', 'business', 'healthcare', 'education', 'culture', 'transport', 'recreation', 'other');
CREATE TYPE location_status_enum AS ENUM ('draft', 'pending', 'published', 'rejected');
CREATE TYPE feature_type_enum AS ENUM ('ramp', 'elevator', 'call_button', 'tactile_path', 'accessible_toilet', 'parking', 'entrance', 'interior', 'signage', 'other');
CREATE TYPE moderation_status_enum AS ENUM ('pending', 'approved', 'rejected');

-- Таблиця roles
CREATE TABLE public.roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    permissions JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Таблиця organizations
CREATE TABLE public.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    type organization_type_enum NOT NULL,
    edrpou VARCHAR(15) UNIQUE,
    website VARCHAR(255),
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    verification_document_url VARCHAR(255),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Таблиця users
CREATE TABLE public.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    role_id UUID REFERENCES public.roles(id),
    gov_id VARCHAR(255) UNIQUE,
    verification_status verification_status_enum NOT NULL DEFAULT 'unverified',
    avatar_url VARCHAR(255),
    organization_id UUID REFERENCES public.organizations(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    last_login_at TIMESTAMP,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- Таблиця locations
CREATE TABLE public.locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    address VARCHAR(500) NOT NULL,
    coordinates GEOMETRY(Point, 4326) NOT NULL,
    type location_type_enum NOT NULL,
    category VARCHAR(100),
    description TEXT,
    contacts JSONB,
    working_hours JSONB,
    created_by UUID REFERENCES public.users(id),
    organization_id UUID REFERENCES public.organizations(id),
    status location_status_enum NOT NULL DEFAULT 'draft',
    overall_accessibility_score INTEGER,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    last_verified_at TIMESTAMP,
    rejection_reason TEXT
);

CREATE INDEX idx_locations_coordinates ON public.locations USING GIST (coordinates);

-- Таблиця accessibility_features
CREATE TABLE public.accessibility_features (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID REFERENCES public.locations(id),
    type feature_type_enum NOT NULL,
    subtype VARCHAR(100),
    description TEXT,
    status BOOLEAN NOT NULL,
    quality_rating INTEGER,
    standards_compliance BOOLEAN,
    created_by UUID REFERENCES public.users(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Таблиця photos
CREATE TABLE public.photos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID REFERENCES public.locations(id),
    feature_id UUID REFERENCES public.accessibility_features(id),
    url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500) NOT NULL,
    description TEXT,
    created_by UUID REFERENCES public.users(id),
    moderation_status moderation_status_enum NOT NULL DEFAULT 'pending',
    ai_moderation_score FLOAT,
    ai_accessibility_detection JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    reject_reason TEXT,
    metadata JSONB
);

-- Таблиця verifications
CREATE TABLE public.verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID REFERENCES public.locations(id),
    feature_id UUID REFERENCES public.accessibility_features(id),
    verified_by UUID REFERENCES public.users(id),
    organization_id UUID REFERENCES public.organizations(id),
    status BOOLEAN NOT NULL,
    comment TEXT,
    evidence_photo_id UUID REFERENCES public.photos(id),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    is_official BOOLEAN NOT NULL DEFAULT FALSE
);

-- Таблиця reviews
CREATE TABLE public.reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID REFERENCES public.locations(id),
    user_id UUID REFERENCES public.users(id),
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    accessibility_experience TEXT,
    moderation_status moderation_status_enum NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Таблиця notifications
CREATE TABLE public.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES public.users(id),
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    link VARCHAR(500),
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Таблиця audit_logs
CREATE TABLE public.audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES public.users(id),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id UUID,
    details JSONB,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);