-- Criar schema gpi
CREATE SCHEMA IF NOT EXISTS gpi;

-- Tabela organizations
CREATE TABLE IF NOT EXISTS gpi.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Inserir organização padrão
INSERT INTO gpi.organizations (id, name) 
VALUES ('e47e6210-4879-4e5b-bf21-9285d2713123', 'Organização Migrada')
ON CONFLICT (id) DO NOTHING;
CREATE TABLE IF NOT EXISTS gpi.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    logto_id TEXT UNIQUE,
    email TEXT NOT NULL,
    name TEXT,
    role TEXT DEFAULT 'user',
    is_banned BOOLEAN DEFAULT false,
    last_seen_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS gpi.projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT NOT NULL,
    client TEXT,
    start_date DATE,
    end_date DATE,
    environment TEXT,
    technician TEXT,
    weight_kg DECIMAL(10,2),
    status TEXT DEFAULT 'active' CHECK (status IN ('active', 'archived')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela parts
CREATE TABLE IF NOT EXISTS gpi.parts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    project_id UUID REFERENCES gpi.projects(id) ON DELETE CASCADE,
    description TEXT,
    dimensions TEXT,
    weight DECIMAL(10,3),
    type TEXT,
    area DECIMAL(10,3),
    complexity INTEGER DEFAULT 1,
    quantity INTEGER NOT NULL DEFAULT 1,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela painting_schemes
CREATE TABLE IF NOT EXISTS gpi.painting_schemes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    project_id UUID REFERENCES gpi.projects(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    type TEXT,
    coat TEXT,
    solids_volume DECIMAL(12,3),
    yield_theoretical DECIMAL(12,3),
    eps_min DECIMAL(12,3),
    eps_max DECIMAL(12,3),
    dilution DECIMAL(12,3),
    manufacturer TEXT,
    color TEXT,
    paint_consumption DECIMAL(12,3),
    thinner_consumption DECIMAL(12,3),
    paint_id TEXT,
    thinner_id TEXT,
    color_hex TEXT,
    thinner_symbol TEXT,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela application_records
CREATE TABLE IF NOT EXISTS gpi.application_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    project_id UUID REFERENCES gpi.projects(id) ON DELETE CASCADE,
    coat_stage TEXT NOT NULL,
    piece_description TEXT,
    date DATE,
    operator TEXT,
    real_weight DECIMAL(10,3),
    volume_used DECIMAL(10,3),
    area_painted DECIMAL(10,3),
    wet_thickness_avg DECIMAL(6,2),
    dry_thickness_calc DECIMAL(6,2),
    real_yield DECIMAL(10,3),
    method TEXT,
    diluent_used DECIMAL(10,3),
    items JSONB,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela inspections
CREATE TABLE IF NOT EXISTS gpi.inspections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    project_id UUID REFERENCES gpi.projects(id) ON DELETE CASCADE,
    application_record_id UUID REFERENCES gpi.application_records(id),
    stock_item_id TEXT,
    instrument_id TEXT,
    type TEXT CHECK (type IN ('painting', 'surface_treatment')),
    date DATE,
    inspector TEXT,
    part_temperature DECIMAL(6,2),
    weight_kg DECIMAL(10,3),
    appearance TEXT,
    defects TEXT,
    photos TEXT[],
    piece_description TEXT,
    eps_points DECIMAL(6,2)[],
    adhesion_test TEXT,
    batch TEXT,
    treatment_executor TEXT,
    treatment_type TEXT,
    cleaning_degree TEXT,
    roughness_readings DECIMAL(6,2)[],
    flash_rust TEXT,
    temperature DECIMAL(6,2),
    relative_humidity DECIMAL(5,2),
    period TEXT CHECK (period IN ('morning', 'afternoon', 'night')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela technical_data_sheets
CREATE TABLE IF NOT EXISTS gpi.technical_data_sheets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT NOT NULL,
    manufacturer TEXT,
    type TEXT,
    file_url TEXT,
    upload_date DATE,
    solids_volume DECIMAL(12,3),
    density DECIMAL(12,3),
    mixing_ratio TEXT,
    yield_theoretical DECIMAL(12,3),
    wft_min DECIMAL(12,3),
    wft_max DECIMAL(12,3),
    dft_min DECIMAL(12,3),
    dft_max DECIMAL(12,3),
    reducer TEXT,
    mixing_ratio_weight TEXT,
    mixing_ratio_volume TEXT,
    dft_reference DECIMAL(12,3),
    yield_factor DECIMAL(12,3),
    dilution DECIMAL(12,3),
    notes TEXT,
    manufacturer_code TEXT,
    min_stock DECIMAL(12,3),
    typical_application TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela yield_studies
CREATE TABLE IF NOT EXISTS gpi.yield_studies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT NOT NULL,
    data_sheet_id TEXT NOT NULL,
    target_dft DECIMAL(6,2) NOT NULL,
    dilution_percent DECIMAL(5,2) NOT NULL,
    categories JSONB NOT NULL,
    total_weight DECIMAL(10,3),
    estimated_paint_volume DECIMAL(10,3),
    estimated_reducer_volume DECIMAL(10,3),
    estimated_paint_volume_by_area DECIMAL(10,3),
    estimated_reducer_volume_by_area DECIMAL(10,3),
    average_complexity DECIMAL(3,1),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela instruments
CREATE TABLE IF NOT EXISTS gpi.instruments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    serial_number TEXT,
    manufacturer TEXT,
    model TEXT,
    last_calibration DATE,
    next_calibration DATE,
    status TEXT DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela stock_items
CREATE TABLE IF NOT EXISTS gpi.stock_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT,
    type TEXT,
    batch_number TEXT,
    quantity DECIMAL(12,3) DEFAULT 0,
    unit TEXT DEFAULT 'L',
    data_sheet_id TEXT,
    location TEXT,
    expiration_date DATE,
    status TEXT DEFAULT 'available',
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela stock_movements
CREATE TABLE IF NOT EXISTS gpi.stock_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    stock_item_id UUID REFERENCES gpi.stock_items(id) ON DELETE CASCADE,
    type TEXT NOT NULL CHECK (type IN ('in', 'out', 'adjustment')),
    quantity DECIMAL(10,3) NOT NULL,
    reason TEXT,
    performed_by TEXT,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela notifications
CREATE TABLE IF NOT EXISTS gpi.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    type TEXT DEFAULT 'info' CHECK (type IN ('info', 'warning', 'error', 'success')),
    is_read BOOLEAN DEFAULT false,
    is_archived BOOLEAN DEFAULT false,
    archived_by TEXT[],
    deleted_by TEXT[],
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela geometry_types
CREATE TABLE IF NOT EXISTS gpi.geometry_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    name TEXT NOT NULL,
    efficiency_loss DECIMAL(5,2),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela messages
CREATE TABLE IF NOT EXISTS gpi.messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    from_user_id UUID,
    to_user_id UUID,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT false,
    read_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela stock_audit_logs
CREATE TABLE IF NOT EXISTS gpi.stock_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    stock_item_id TEXT,
    action TEXT NOT NULL,
    quantity_before DECIMAL(12,3),
    quantity_after DECIMAL(12,3),
    performed_by TEXT,
    details TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela system_settings
CREATE TABLE IF NOT EXISTS gpi.system_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    key TEXT UNIQUE NOT NULL,
    value JSONB,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela stored_files (Metadados dos PDFs)
CREATE TABLE IF NOT EXISTS gpi.stored_files (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    filename TEXT NOT NULL,
    mime_type TEXT,
    size_bytes BIGINT,
    storage_path TEXT, -- Caminho no Supabase Storage
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Habilitar PostgREST para o schema gpi
GRANT USAGE ON SCHEMA gpi TO postgres, anon, authenticated, service_role;

-- Grant permissions em todas as tabelas
GRANT ALL ON TABLE gpi.organizations TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.users TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.projects TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.parts TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.painting_schemes TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.application_records TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.inspections TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.technical_data_sheets TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.yield_studies TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.instruments TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.stock_items TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.stock_movements TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.notifications TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.geometry_types TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.messages TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.stock_audit_logs TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.system_settings TO postgres, anon, authenticated, service_role;
GRANT ALL ON TABLE gpi.stored_files TO postgres, anon, authenticated, service_role;

-- Grant sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA gpi TO postgres, anon, authenticated, service_role;

SELECT 'Schema gpi criado com sucesso!' AS result;