Skip to content

Instantly share code, notes, and snippets.

@zouhir
Last active January 14, 2025 04:04
Show Gist options
  • Save zouhir/cc7f12a5296b210195deef286f578ea6 to your computer and use it in GitHub Desktop.
Save zouhir/cc7f12a5296b210195deef286f578ea6 to your computer and use it in GitHub Desktop.
-- 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();
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"`
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment