-
-
Save zouhir/cc7f12a5296b210195deef286f578ea6 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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(); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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