gistfile1.txt

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Reports table to track individual submissions
CREATE TABLE reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL REFERENCES users(id),
    location_code VARCHAR(50) NOT NULL,  -- References the geo location codes from the existing system
    report_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Electricity availability reports
CREATE TABLE electricity_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    hours_lost INTEGER NOT NULL CHECK (hours_lost >= 0 AND hours_lost <= 24),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Water availability reports
CREATE TABLE water_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    hours_lost INTEGER NOT NULL CHECK (hours_lost >= 0 AND hours_lost <= 24),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Fuel types lookup table
CREATE TABLE fuel_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    name_ar VARCHAR(50) NOT NULL UNIQUE,
    unit VARCHAR(20) NOT NULL,  -- e.g., 'liter', 'kg'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert common fuel types
INSERT INTO fuel_types (name, name_ar, unit) VALUES
    ('Diesel', 'مازوت', 'liter'),
    ('Gasoline', 'بنزين', 'liter'),
    ('Propane', 'غاز', 'kg');

-- Fuel availability reports
CREATE TABLE fuel_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    fuel_type_id INTEGER NOT NULL REFERENCES fuel_types(id),
    is_available BOOLEAN NOT NULL,
    price_per_unit DECIMAL(10,2),  -- Null if not available
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(report_id, fuel_type_id)  -- One report per fuel type
);

-- Medicine categories lookup table
CREATE TABLE medicine_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    name_ar VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert common medicine categories
INSERT INTO medicine_categories (name, name_ar) VALUES
    ('Chronic Disease Medication', 'أدوية الأمراض المزمنة'),
    ('Pain Relief', 'مسكنات الألم'),
    ('Antibiotics', 'المضادات الحيوية'),
    ('Heart Medication', 'أدوية القلب'),
    ('Diabetes Medication', 'أدوية السكري'),
    ('Blood Pressure Medication', 'أدوية ضغط الدم');

-- Medicine reports
CREATE TABLE medicine_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    category_id INTEGER NOT NULL REFERENCES medicine_categories(id),
    medicine_name VARCHAR(200),  -- Optional specific medicine name
    medicine_name_ar VARCHAR(200),  -- Optional specific medicine name in Arabic
    is_available BOOLEAN NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Bread types lookup table
CREATE TABLE bread_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    name_ar VARCHAR(50) NOT NULL UNIQUE,
    unit VARCHAR(20) NOT NULL,  -- e.g., 'bundle', 'piece'
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert common bread types
INSERT INTO bread_types (name, name_ar, unit) VALUES
    ('Regular Flatbread', 'خبز عربي عادي', 'bundle'),
    ('Tourist Bread', 'خبز سياحي', 'piece');

-- Bread availability reports
CREATE TABLE bread_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    bread_type_id INTEGER NOT NULL REFERENCES bread_types(id),
    is_available BOOLEAN NOT NULL,
    price_per_unit DECIMAL(10,2),  -- Null if not available
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(report_id, bread_type_id)  -- One report per bread type
);

-- Indexes for better query performance
CREATE INDEX idx_reports_location ON reports(location_code);
CREATE INDEX idx_reports_date ON reports(report_date);
CREATE INDEX idx_reports_user ON reports(user_id);
CREATE INDEX idx_electricity_report ON electricity_reports(report_id);
CREATE INDEX idx_water_report ON water_reports(report_id);
CREATE INDEX idx_fuel_report ON fuel_reports(report_id);
CREATE INDEX idx_medicine_report ON medicine_reports(report_id);
CREATE INDEX idx_bread_report ON bread_reports(report_id);

-- Trigger to update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_reports_updated_at
    BEFORE UPDATE ON reports
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

V2

package report

import (
    "time"
    "github.com/google/uuid"
    "github.com/zouhir/syria-open-data/internal/domain"
)

// Constants for validation and business logic
const (
    MaxQualityScore     = 100
    MinQualityScore     = 0
    MaxAvailabilityScore = 100
    MinAvailabilityScore = 0
    MaxDurationHours    = 24
    MinDurationHours    = 0
)

// InfrastructureType defines valid infrastructure types
type InfrastructureType string

const (
    InfrastructureWater       InfrastructureType = "water"
    InfrastructureElectricity InfrastructureType = "electricity"
    InfrastructureInternet    InfrastructureType = "internet"
)

// MaterialType defines valid material types
type MaterialType string

const (
    MaterialDiesel  MaterialType = "diesel"
    MaterialGas     MaterialType = "gas"
    MaterialBread   MaterialType = "bread"
    MaterialFlour   MaterialType = "flour"
)

// Reports represents the base report entity
type Reports struct {
    ID            uuid.UUID       `json:"id" gorm:"primaryKey;type:uuid"`
    UserID        string          `json:"user_id" gorm:"type:varchar(255);not null;index"`
    Location      domain.Location `json:"location" gorm:"type:jsonb"`
    LocationCode  string          `json:"location_code" gorm:"type:varchar(255);not null;index:idx_location_date,priority:1"`
    ReportDate    time.Time       `json:"report_date" gorm:"index:idx_location_date,priority:2"`
    CreatedAt     time.Time       `json:"created_at" gorm:"autoCreateTime"`
    UpdatedAt     time.Time       `json:"updated_at" gorm:"autoUpdateTime"`
    DeletedAt     *time.Time      `json:"deleted_at" gorm:"index"`
    Status        string          `json:"status" gorm:"type:varchar(20);default:'active'"`
    Version       int             `json:"version" gorm:"default:1"`
    
    // Vote tracking
    UpvoteCount   int32          `json:"upvote_count" gorm:"default:0"`
    DownvoteCount int32          `json:"downvote_count" gorm:"default:0"`
    VoteScore     int32          `json:"vote_score" gorm:"default:0"`
    LastVoteAt    *time.Time     `json:"last_vote_at"`
    
    // Reliability tracking (for moderation e.g. a vote can get a lot of upvotes but is inaccurate, moderators can use this)
    FlagsCount       int32    `json:"flags_count" gorm:"default:0"`
    LastFlagAt       *time.Time `json:"last_flag_at"`
    ReliabilityScore float32  `json:"reliability_score" gorm:"default:0"`
    
    // Metadata
    IPAddress string          `json:"ip_address" gorm:"type:varchar(45)"`
    UserAgent string          `json:"user_agent" gorm:"type:varchar(255)"`
    Metadata  domain.Metadata `json:"metadata" gorm:"type:jsonb"`
}

// ReportVote tracks individual votes on reports
type ReportVote struct {
    ID        uuid.UUID `json:"id" gorm:"primaryKey;type:uuid"`
    ReportID  uuid.UUID `json:"report_id" gorm:"type:uuid;not null;index"`
    UserID    string    `json:"user_id" gorm:"type:varchar(255);not null;index"`
    VoteType  int8      `json:"vote_type" gorm:"type:smallint;not null"` // 1 for upvote, -1 for downvote
    CreatedAt time.Time `json:"created_at" gorm:"autoCreateTime"`
}

// InfrastructureReport represents infrastructure availability reports
type InfrastructureReport struct {
    ID                 uuid.UUID          `json:"id" gorm:"primaryKey;type:uuid"`
    Report             Reports            `json:"report" gorm:"foreignKey:ReportID"`
    ReportID           uuid.UUID          `json:"report_id" gorm:"type:uuid;not null;index"`
    InfrastructureType InfrastructureType `json:"infrastructure_type" gorm:"type:varchar(255);not null;index"`
    Duration           int                `json:"duration" gorm:"type:int;check:duration >= 0 AND duration <= 24"`
    Quality           int                 `json:"quality" gorm:"type:int;check:quality >= 0 AND quality <= 100"`
    CreatedAt         time.Time           `json:"created_at" gorm:"autoCreateTime"`
}

// MaterialReport represents material availability reports
type MaterialReport struct {
    ID           uuid.UUID    `json:"id" gorm:"primaryKey;type:uuid"`
    Report       Reports      `json:"report" gorm:"foreignKey:ReportID"`
    ReportID     uuid.UUID    `json:"report_id" gorm:"type:uuid;not null;index"`
    Material     MaterialType `json:"material" gorm:"type:varchar(255);not null;index"`
    Unit         string       `json:"unit" gorm:"type:varchar(255);not null"`
    Price        float64      `json:"price" gorm:"type:decimal(10,2);not null"`
    Availability int          `json:"availability" gorm:"type:int;check:availability >= 0 AND availability <= 100"`
    CreatedAt    time.Time    `json:"created_at" gorm:"autoCreateTime"`
}

// MedicalReport represents medical supplies availability reports
type MedicalReport struct {
    ID           uuid.UUID `json:"id" gorm:"primaryKey;type:uuid"`
    Report       Reports   `json:"report" gorm:"foreignKey:ReportID"`
    ReportID     uuid.UUID `json:"report_id" gorm:"type:uuid;not null;index"`
    Category     string    `json:"category" gorm:"type:varchar(200);not null;index"`
    SubCategory  string    `json:"sub_category" gorm:"type:varchar(200);not null;index"`
    ItemName     string    `json:"item_name" gorm:"type:varchar(200);not null"`
    Availability int       `json:"availability" gorm:"type:int;check:availability >= 0 AND availability <= 100"`
    Notes        string    `json:"notes" gorm:"type:text"`
    CreatedAt    time.Time `json:"created_at" gorm:"autoCreateTime"`
}

// UserReputation tracks user reliability scores
type UserReputation struct {
    UserID            string    `json:"user_id" gorm:"primaryKey;type:varchar(255)"`
    ReputationScore   int32     `json:"reputation_score" gorm:"default:0"`
    ReportsCount      int32     `json:"reports_count" gorm:"default:0"`
    UpvotesReceived   int32     `json:"upvotes_received" gorm:"default:0"`
    DownvotesReceived int32     `json:"downvotes_received" gorm:"default:0"`
    LastUpdated       time.Time `json:"last_updated" gorm:"autoUpdateTime"`
}



////////
///////
// In internal/domain/metadata.go
package domain

// Metadata represents flexible additional data we might want to store
type Metadata struct {
    // For client-side info
    Browser     string `json:"browser,omitempty"`
    DeviceType  string `json:"device_type,omitempty"`
    Platform    string `json:"platform,omitempty"`
    
    // For location context
    Coordinates *struct {
        Latitude  float64 `json:"lat,omitempty"`
        Longitude float64 `json:"lng,omitempty"`
    } `json:"coordinates,omitempty"`
    
    // For moderation
    ModeratorNotes string `json:"moderator_notes,omitempty"`
    ReportSource   string `json:"report_source,omitempty"`  // e.g., "web", "mobile", "api"
    
    // For any additional data we might need in the future
    Extra map[string]interface{} `json:"extra,omitempty"`
}