ساختار پایگاه داده (اسامی جداول با فیلدهای مورد نیاز) یک سامانه درج آگهی در MySQL

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50) UNIQUE NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Phone VARCHAR(15),
    Avatar VARCHAR(255),
    UserType ENUM('Normal', 'Premium', 'Admin', 'SuperAdmin') DEFAULT 'Normal',
    IsActive BOOLEAN DEFAULT TRUE,
    IsVerified BOOLEAN DEFAULT FALSE,
    VerificationToken VARCHAR(100),
    ResetPasswordToken VARCHAR(100),
    LastLogin DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (Email),
    INDEX idx_user_type (UserType),
    INDEX idx_created (CreatedAt)
);

CREATE TABLE UserProfiles (
    ProfileID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    CompanyName NVARCHAR(200),
    Website VARCHAR(200),
    Address TEXT,
    City NVARCHAR(50),
    Province NVARCHAR(50),
    PostalCode VARCHAR(10),
    Bio TEXT,
    SocialMedia JSON, -- {facebook: '', twitter: '', instagram: '', linkedin: ''}
    NotificationPreferences JSON, -- {email: true, sms: false, push: true}
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE,
    UNIQUE KEY unique_user (UserID)
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName NVARCHAR(100) NOT NULL,
    CategorySlug VARCHAR(100) UNIQUE NOT NULL,
    ParentCategoryID INT NULL,
    Description TEXT,
    Icon VARCHAR(100),
    IsActive BOOLEAN DEFAULT TRUE,
    SortOrder INT DEFAULT 0,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ParentCategoryID) REFERENCES Categories(CategoryID),
    INDEX idx_parent (ParentCategoryID),
    INDEX idx_slug (CategorySlug)
);

CREATE TABLE Advertisements (
    AdID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    CategoryID INT NOT NULL,
    Title NVARCHAR(200) NOT NULL,
    Slug VARCHAR(250) UNIQUE NOT NULL,
    Description TEXT NOT NULL,
    ShortDescription NVARCHAR(500),
    Price DECIMAL(15,2),
    Currency VARCHAR(3) DEFAULT 'IRT',
    IsNegotiable BOOLEAN DEFAULT FALSE,
    ContactInfo JSON NOT NULL, -- {phone: '', email: '', telegram: '', instagram: ''}
    LocationInfo JSON, -- {address: '', city: '', province: '', latitude: '', longitude: ''}
    Status ENUM('Draft', 'Pending', 'Approved', 'Rejected', 'Expired', 'Sold') DEFAULT 'Draft',
    IsPremium BOOLEAN DEFAULT FALSE,
    IsUrgent BOOLEAN DEFAULT FALSE,
    ExpiryDate DATETIME,
    ViewCount INT DEFAULT 0,
    LikeCount INT DEFAULT 0,
    FeaturedUntil DATETIME NULL,
    RejectionReason TEXT,
    ApprovedBy INT NULL,
    ApprovedAt DATETIME,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
    FOREIGN KEY (ApprovedBy) REFERENCES Users(UserID),
    INDEX idx_user (UserID),
    INDEX idx_category (CategoryID),
    INDEX idx_status (Status),
    INDEX idx_created (CreatedAt),
    INDEX idx_expiry (ExpiryDate),
    INDEX idx_premium (IsPremium),
    INDEX idx_featured (FeaturedUntil),
    FULLTEXT idx_search (Title, Description, ShortDescription)
);

CREATE TABLE AdImages (
    ImageID INT PRIMARY KEY AUTO_INCREMENT,
    AdID INT NOT NULL,
    ImagePath VARCHAR(500) NOT NULL,
    ImageThumbnail VARCHAR(500),
    ImageAlt NVARCHAR(200),
    IsPrimary BOOLEAN DEFAULT FALSE,
    SortOrder INT DEFAULT 0,
    UploadedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (AdID) REFERENCES Advertisements(AdID) ON DELETE CASCADE,
    INDEX idx_ad (AdID),
    INDEX idx_primary (IsPrimary)
);

CREATE TABLE AdAttributes (
    AttributeID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryID INT NOT NULL,
    AttributeName NVARCHAR(100) NOT NULL,
    AttributeType ENUM('Text', 'Number', 'Select', 'Boolean', 'Date') DEFAULT 'Text',
    IsRequired BOOLEAN DEFAULT FALSE,
    Options JSON, -- برای نوع Select
    SortOrder INT DEFAULT 0,
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
    INDEX idx_category (CategoryID)
);

CREATE TABLE AdAttributeValues (
    ValueID BIGINT PRIMARY KEY AUTO_INCREMENT,
    AdID INT NOT NULL,
    AttributeID INT NOT NULL,
    StringValue NVARCHAR(500),
    NumberValue DECIMAL(15,2),
    BooleanValue BOOLEAN,
    DateValue DATE,
    
    FOREIGN KEY (AdID) REFERENCES Advertisements(AdID) ON DELETE CASCADE,
    FOREIGN KEY (AttributeID) REFERENCES AdAttributes(AttributeID),
    UNIQUE KEY unique_ad_attribute (AdID, AttributeID),
    INDEX idx_ad (AdID),
    INDEX idx_attribute (AttributeID)
);

CREATE TABLE Reviews (
    ReviewID INT PRIMARY KEY AUTO_INCREMENT,
    AdID INT NOT NULL,
    UserID INT NOT NULL, -- کاربری که نظر می‌دهد
    Rating TINYINT NOT NULL CHECK (Rating >= 1 AND Rating <= 5),
    Comment TEXT,
    IsApproved BOOLEAN DEFAULT FALSE,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (AdID) REFERENCES Advertisements(AdID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    UNIQUE KEY unique_ad_user (AdID, UserID),
    INDEX idx_ad (AdID),
    INDEX idx_user (UserID),
    INDEX idx_rating (Rating)
);

CREATE TABLE Favorites (
    FavoriteID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    AdID INT NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE,
    FOREIGN KEY (AdID) REFERENCES Advertisements(AdID) ON DELETE CASCADE,
    UNIQUE KEY unique_user_ad (UserID, AdID),
    INDEX idx_user (UserID),
    INDEX idx_ad (AdID)
);

CREATE TABLE UserPackages (
    PackageID INT PRIMARY KEY AUTO_INCREMENT,
    PackageName NVARCHAR(100) NOT NULL,
    PackageType ENUM('Free', 'Silver', 'Gold', 'Platinum') DEFAULT 'Free',
    MaxAds INT DEFAULT 5,
    MaxImagesPerAd INT DEFAULT 3,
    CanPremium BOOLEAN DEFAULT FALSE,
    CanUrgent BOOLEAN DEFAULT FALSE,
    AutoApprove BOOLEAN DEFAULT FALSE,
    Price DECIMAL(10,2) DEFAULT 0,
    DurationDays INT DEFAULT 30,
    IsActive BOOLEAN DEFAULT TRUE,
    Features JSON, -- لیست امکانات
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE UserSubscriptions (
    SubscriptionID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    PackageID INT NOT NULL,
    StartDate DATETIME NOT NULL,
    EndDate DATETIME NOT NULL,
    IsActive BOOLEAN DEFAULT TRUE,
    PurchasedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (PackageID) REFERENCES UserPackages(PackageID),
    INDEX idx_user (UserID),
    INDEX idx_active (IsActive),
    INDEX idx_end_date (EndDate)
);

CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    PackageID INT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    Currency VARCHAR(3) DEFAULT 'IRT',
    PaymentMethod ENUM('Online', 'BankTransfer', 'Credit') DEFAULT 'Online',
    PaymentStatus ENUM('Pending', 'Completed', 'Failed', 'Refunded') DEFAULT 'Pending',
    PaymentGateway VARCHAR(50),
    GatewayReference VARCHAR(100),
    Description TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (PackageID) REFERENCES UserPackages(PackageID),
    INDEX idx_user (UserID),
    INDEX idx_status (PaymentStatus),
    INDEX idx_created (CreatedAt)
);

CREATE TABLE AdViews (
    ViewID BIGINT PRIMARY KEY AUTO_INCREMENT,
    AdID INT NOT NULL,
    UserID INT NULL, -- اگر کاربر لاگین کرده باشد
    IPAddress VARCHAR(45),
    UserAgent TEXT,
    ViewDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (AdID) REFERENCES Advertisements(AdID),
    INDEX idx_ad (AdID),
    INDEX idx_date (ViewDate)
);

CREATE TABLE UserActivities (
    ActivityID BIGINT PRIMARY KEY AUTO_INCREMENT,
    UserID INT NOT NULL,
    ActivityType ENUM('Login', 'AdCreate', 'AdUpdate', 'AdDelete', 'ProfileUpdate') DEFAULT 'Login',
    Description TEXT,
    IPAddress VARCHAR(45),
    UserAgent TEXT,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    INDEX idx_user (UserID),
    INDEX idx_type (ActivityType),
    INDEX idx_created (CreatedAt)
);

CREATE TABLE SystemSettings (
    SettingID INT PRIMARY KEY AUTO_INCREMENT,
    SettingKey VARCHAR(100) UNIQUE NOT NULL,
    SettingValue TEXT,
    SettingType ENUM('String', 'Number', 'Boolean', 'JSON') DEFAULT 'String',
    Description TEXT,
    UpdatedBy INT NULL,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (UpdatedBy) REFERENCES Users(UserID)
);

-- درج دسته‌بندی‌های اصلی
INSERT INTO Categories (CategoryName, CategorySlug, Description, SortOrder) VALUES
('املاک', 'real-estate', 'آگهی‌های مربوط به خرید و فروش و رهن اجاره املاک', 1),
('وسایل نقلیه', 'vehicles', 'خودرو، موتورسیکلت، قطعات یدکی', 2),
('کالای دیجیتال', 'digital-goods', 'موبایل، تبلت، لپ‌تاپ، کامپیوتر', 3),
('خانه و آشپزخانه', 'home-kitchen', 'لوازم خانگی، آشپزخانه، دکوراسیون', 4),
('خدمات', 'services', 'خدمات مختلف آموزشی، درمانی، فنی و ...', 5);

-- درج بسته‌های کاربری
INSERT INTO UserPackages (PackageName, PackageType, MaxAds, MaxImagesPerAd, CanPremium, CanUrgent, AutoApprove, Price, DurationDays, Features) VALUES
('رایگان', 'Free', 5, 3, FALSE, FALSE, FALSE, 0, 30, '["آگهی معمولی", "3 تصویر", "مدت 30 روز"]'),
('نقره‌ای', 'Silver', 20, 8, TRUE, FALSE, FALSE, 50000, 30, '["20 آگهی", "8 تصویر", "آگهی ویژه", "مدت 30 روز"]'),
('طلایی', 'Gold', 50, 15, TRUE, TRUE, TRUE, 100000, 30, '["50 آگهی", "15 تصویر", "آگهی ویژه", "آگهی فوری", "تایید خودکار", "مدت 30 روز"]'),
('پلاتینیوم', 'Platinum', 999, 20, TRUE, TRUE, TRUE, 200000, 30, '["آگهی نامحدود", "20 تصویر", "تمام امکانات", "تایید خودکار", "پشتیبانی ویژه"]');

-- درج تنظیمات سیستم
INSERT INTO SystemSettings (SettingKey, SettingValue, SettingType, Description) VALUES
('site_title', 'سامانه آگهی', 'String', 'عنوان سایت'),
('default_ad_expiry_days', '30', 'Number', 'مدت اعتبار پیش‌فرض آگهی‌ها'),
('auto_approve_premium_users', 'true', 'Boolean', 'تایید خودکار آگهی کاربران ویژه'),
('max_images_per_ad', '5', 'Number', 'حداکثر تصویر مجاز برای هر آگهی'),
('contact_email', 'admin@example.com', 'String', 'ایمیل پشتیبانی');

-- ایجاد کاربر ادمین (پسورد: admin123)
INSERT INTO Users (Username, Email, PasswordHash, FirstName, LastName, UserType, IsVerified) 
VALUES ('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'مدیر', 'سیستم', 'SuperAdmin', TRUE);

-- ایجاد پروفایل برای ادمین
INSERT INTO UserProfiles (UserID, CompanyName, Phone) 
VALUES (1, 'سامانه آگهی', '02112345678');