-- SQLite Schema for GulfSportz

-- Table: users
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    user_type TEXT NOT NULL,
    email_verified INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table: club_profiles
CREATE TABLE club_profiles (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    club_name TEXT NOT NULL UNIQUE,
    address TEXT,
    established_year INTEGER,
    description TEXT,
    website TEXT,
    phone TEXT,
    logo_url TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table: player_profiles
CREATE TABLE player_profiles (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE,
    position TEXT,
    jersey_number INTEGER,
    bio TEXT,
    phone TEXT,
    profile_image_url TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table: sports
CREATE TABLE sports (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    description TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table: matches
CREATE TABLE matches (
    id TEXT PRIMARY KEY,
    sport_id TEXT NOT NULL,
    home_team_id TEXT,
    away_team_id TEXT,
    match_date DATETIME NOT NULL,
    venue TEXT,
    status TEXT DEFAULT 'scheduled',
    home_score INTEGER,
    away_score INTEGER,
    coverage_type TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sport_id) REFERENCES sports(id),
    FOREIGN KEY (home_team_id) REFERENCES club_profiles(id),
    FOREIGN KEY (away_team_id) REFERENCES club_profiles(id)
);

-- Table: player_statistics
CREATE TABLE player_statistics (
    id TEXT PRIMARY KEY,
    player_id TEXT NOT NULL,
    match_id TEXT NOT NULL,
    points INTEGER,
    tries INTEGER,
    assists INTEGER,
    minutes_played INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (player_id) REFERENCES player_profiles(id),
    FOREIGN KEY (match_id) REFERENCES matches(id)
);

-- Table: articles
CREATE TABLE articles (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    excerpt TEXT,
    content TEXT NOT NULL,
    author_id TEXT,
    sport_id TEXT,
    featured_image_url TEXT,
    status TEXT DEFAULT 'draft',
    published_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id),
    FOREIGN KEY (sport_id) REFERENCES sports(id)
);

-- Table: club_players
CREATE TABLE club_players (
    id TEXT PRIMARY KEY,
    club_id TEXT NOT NULL,
    player_id TEXT NOT NULL,
    joined_date DATE,
    left_date DATE,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (club_id) REFERENCES club_profiles(id),
    FOREIGN KEY (player_id) REFERENCES player_profiles(id)
);

-- Table: venues
CREATE TABLE venues (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT NOT NULL,
    address TEXT,
    capacity INTEGER,
    coverage_type TEXT,
    facilities TEXT, -- Store as JSON string
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
