-- Table: users
CREATE TABLE users (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    email character varying(255) NOT NULL,
    password character varying(255) NOT NULL,
    user_type character varying(20) NOT NULL,
    email_verified boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: club_profiles
CREATE TABLE club_profiles (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL,
    club_name character varying(255) NOT NULL,
    address text,
    established_year integer,
    description text,
    website character varying(255),
    phone character varying(50),
    logo_url character varying(500),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: player_profiles
CREATE TABLE player_profiles (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    user_id uuid NOT NULL,
    first_name character varying(100) NOT NULL,
    last_name character varying(100) NOT NULL,
    date_of_birth date,
    position character varying(100),
    jersey_number integer,
    bio text,
    phone character varying(50),
    profile_image_url character varying(500),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: sports
CREATE TABLE sports (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name character varying(100) NOT NULL,
    slug character varying(100) NOT NULL,
    description text,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: matches
CREATE TABLE matches (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    sport_id uuid NOT NULL,
    home_team_id uuid,
    away_team_id uuid,
    match_date timestamp with time zone NOT NULL,
    venue character varying(255),
    status character varying(50) DEFAULT 'scheduled'::character varying,
    home_score integer,
    away_score integer,
    coverage_type character varying(50),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: player_statistics
CREATE TABLE player_statistics (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    player_id uuid NOT NULL,
    match_id uuid NOT NULL,
    points integer,
    tries integer,
    assists integer,
    minutes_played integer,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: articles
CREATE TABLE articles (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    title character varying(500) NOT NULL,
    slug character varying(500) NOT NULL,
    excerpt text,
    content text NOT NULL,
    author_id uuid,
    sport_id uuid,
    featured_image_url character varying(500),
    status character varying(50) DEFAULT 'draft'::character varying,
    published_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: club_players
CREATE TABLE club_players (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    club_id uuid NOT NULL,
    player_id uuid NOT NULL,
    joined_date date,
    left_date date,
    is_active boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Table: venues
CREATE TABLE venues (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name character varying(255) NOT NULL,
    location character varying(255) NOT NULL,
    address text,
    capacity integer,
    coverage_type character varying(50),
    facilities ARRAY,
    status character varying(50) DEFAULT 'active'::character varying,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

