-- Livestock Marketplace schema (PostgreSQL)
-- Generated from specs in README.md

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) UNIQUE NOT NULL,
    location_id UUID NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS species (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS breeds (
    id SERIAL PRIMARY KEY,
    species_id INT NOT NULL REFERENCES species(id) ON DELETE RESTRICT,
    name VARCHAR(100) NOT NULL,
    description_text TEXT,
    description_media_url TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraint: Only one type of description should be allowed
    CONSTRAINT chk_description_type CHECK (
        (description_text IS NOT NULL AND description_media_url IS NULL)
        OR (description_text IS NULL AND description_media_url IS NOT NULL)
        OR (description_text IS NULL AND description_media_url IS NULL)
    )
);


-------------------------------------------------------
-- ENUM for location type
-------------------------------------------------------
CREATE TYPE location_type_enum AS ENUM ('farm', 'home', 'other');
CREATE TYPE location_source_type_enum AS ENUM (
    'gps',
    'manual',
    'imported',
    'unknown'
);


CREATE TABLE IF NOT EXISTS location_details (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    is_saved_address BOOLEAN NOT NULL DEFAULT FALSE,
    location_type location_type_enum NOT NULL,
    country VARCHAR(100),
    state VARCHAR(100),
    district VARCHAR(100),
    city_village VARCHAR(150),
    pincode VARCHAR(20),
    source_type location_source_type_enum NOT NULL DEFAULT 'unknown',
    source_confidence VARCHAR(50) NOT NULL DEFAULT 'medium',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    lat DECIMAL(10,7) NOT NULL,
    lng DECIMAL(10,7) NOT NULL,
    location_detail_id UUID UNIQUE REFERENCES location_details(id) ON DELETE SET NULL,
    selected_location BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Selected location is unique per user, so this trigger ensures that only one location is selected per user
CREATE OR REPLACE FUNCTION enforce_single_selected_location()
RETURNS trigger AS $$
BEGIN
    -- When changing a location to selected (only act if actually setting to TRUE)
    IF NEW.selected_location = TRUE THEN
        -- Unselect all other locations for this user (excluding the current row)
        UPDATE user_locations
        SET selected_location = FALSE
        WHERE user_id = NEW.user_id
          AND id <> NEW.id
          AND selected_location = TRUE;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_single_selected_location
BEFORE INSERT OR UPDATE ON user_locations
FOR EACH ROW
EXECUTE FUNCTION enforce_single_selected_location();


-------------------------------------------------------
-- Add FK constraint after both tables exist
-------------------------------------------------------
ALTER TABLE users
ADD CONSTRAINT fk_users_location
FOREIGN KEY (location_id)
REFERENCES user_locations(id)
ON DELETE SET NULL;

-------------------------------------------------------
-- Indexes
-------------------------------------------------------
CREATE INDEX idx_user_locations_user ON user_locations(user_id);
CREATE INDEX idx_user_locations_selected ON user_locations(selected_location);
CREATE INDEX idx_location_details_type ON location_details(location_type);
CREATE INDEX idx_user_locations_location_detail ON user_locations(location_detail_id);

CREATE TABLE IF NOT EXISTS animals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    species_id INT NOT NULL REFERENCES species(id) ON DELETE RESTRICT,
    breed_id INT REFERENCES breeds(id) ON DELETE SET NULL,
    sex VARCHAR(20) NOT NULL,
    age_months INT,
    weight_kg DECIMAL(10,2),
    color_markings VARCHAR(200),
    quantity INT NOT NULL DEFAULT 1,
    purpose VARCHAR(50) NOT NULL,
    health_status VARCHAR(50) NOT NULL,
    vaccinated BOOLEAN NOT NULL DEFAULT FALSE,
    dewormed BOOLEAN NOT NULL DEFAULT FALSE,
    previous_pregnancies_count INT,
    pregnancy_status VARCHAR(50),
    milk_yield_litre_per_day DECIMAL(10,2),
    ear_tag_no VARCHAR(100),
    description TEXT,
    suggested_care TEXT,
    location_id UUID REFERENCES user_locations(id) ON DELETE SET NULL,
    created_from VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TYPE listing_status_enum AS ENUM (
    'active',
    'expired',
    'hidden',
    'sold',
    'deleted'
);

CREATE TYPE listing_score_status_enum AS ENUM (
    'pending',
    'calculating',
    'completed',
    'failed'
);

CREATE TYPE listing_type_enum AS ENUM (
    'sale',
    'stud_service',
    'adoption',
    'other'
);

CREATE TYPE seller_type_enum AS ENUM (
    'owner',
    'farmer',
    'broker',
    'agent',
    'other'
);

CREATE TABLE IF NOT EXISTS listings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    seller_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    animal_id UUID NOT NULL UNIQUE REFERENCES animals(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    price NUMERIC(12,2) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    is_negotiable BOOLEAN NOT NULL DEFAULT TRUE,
    listing_type listing_type_enum NOT NULL,
    status listing_status_enum NOT NULL DEFAULT 'active',
    listing_score INT NOT NULL DEFAULT 0,
    views_count INT NOT NULL DEFAULT 0,
    bookmarks_count INT NOT NULL DEFAULT 0,
    enquiries_call_count INT NOT NULL DEFAULT 0,
    enquiries_whatsapp_count INT NOT NULL DEFAULT 0,
    clicks_count INT NOT NULL DEFAULT 0,
    listing_score_status listing_score_status_enum NOT NULL DEFAULT 'pending',
    seller_type seller_type_enum NOT NULL DEFAULT 'owner',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS listing_media (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    listing_id UUID NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
    media_url VARCHAR(500) NOT NULL,
    media_type VARCHAR(50) NOT NULL,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS custom_requirements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    requirement_text TEXT NOT NULL,
    animal_id UUID REFERENCES animals(id) ON DELETE SET NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TYPE message_type_enum AS ENUM ('text', 'media', 'both');
CREATE TYPE media_type_enum AS ENUM ('image', 'video', 'audio', 'document');

CREATE TABLE conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id UUID NOT NULL,
    seller_id UUID NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_conversation_buyer
        FOREIGN KEY (buyer_id) REFERENCES users(id),

    CONSTRAINT fk_conversation_seller
        FOREIGN KEY (seller_id) REFERENCES users(id),

    CONSTRAINT unique_buyer_seller UNIQUE (buyer_id, seller_id)
);

CREATE TABLE conversation_media (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    media_type media_type_enum NOT NULL,    -- image | video | audio | document
    media_url TEXT NOT NULL,
    thumbnail_url TEXT NULL,
    duration_sec INT NULL,                   -- audio/video only
    file_size_kb INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL,
    sender_id UUID NOT NULL,
    receiver_id UUID NOT NULL,

    message_type message_type_enum NOT NULL DEFAULT 'text',

    content TEXT NULL,         -- nullable for media-only messages
    media_id UUID UNIQUE,        -- FK for media, enforce 1:1 relationship

    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    CONSTRAINT fk_message_conversation
        FOREIGN KEY (conversation_id) REFERENCES conversations(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_message_sender
        FOREIGN KEY (sender_id) REFERENCES users(id),

    CONSTRAINT fk_message_receiver
        FOREIGN KEY (receiver_id) REFERENCES users(id),

    CONSTRAINT fk_message_media
        FOREIGN KEY (media_id) REFERENCES conversation_media(id)
        ON DELETE SET NULL,

    -- Ensures consistency between message_type and media usage
    CONSTRAINT chk_message_media_relation CHECK (
        (message_type = 'text' AND media_id IS NULL) OR
        (message_type = 'media' AND media_id IS NOT NULL AND content IS NULL) OR
        (message_type = 'both' AND media_id IS NOT NULL AND content IS NOT NULL)
    )
);

CREATE INDEX idx_conversations_buyer ON conversations(buyer_id);
CREATE INDEX idx_conversations_seller ON conversations(seller_id);
CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_sender ON messages(sender_id);
CREATE INDEX idx_messages_media ON messages(media_id);


CREATE TYPE communication_type_enum AS ENUM ('call', 'missed_call', 'voicemail');
CREATE TYPE call_status_enum AS ENUM (
    'initiated',
    'ringing',
    'answered',
    'completed',
    'failed',
    'busy',
    'no_answer'
);

CREATE TABLE communication_records (
    communication_id BIGSERIAL PRIMARY KEY,

    conversation_id UUID NOT NULL,      -- Conversation this communication belongs to
    buyer_id UUID NOT NULL,             -- Buyer involved
    seller_id UUID NOT NULL,            -- Seller involved
    request_id UUID NULL,               -- Optional: Buy request this communication refers to

    communication_type communication_type_enum NOT NULL,
    cpaas_call_id VARCHAR(100) NULL,   -- Reference ID returned by CPaaS provider

    call_status call_status_enum NOT NULL,

    start_time TIMESTAMP NULL,          -- Time CPaaS initiated call
    end_time TIMESTAMP NULL,            -- Time call ended
    duration_seconds INT DEFAULT 0,     -- Duration calculated

    recording_url TEXT NULL,            -- Optional CPaaS recording URL
    metadata JSON NULL,                 -- Any extra payload

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Foreign Keys
    CONSTRAINT fk_comm_conversation
        FOREIGN KEY (conversation_id)
        REFERENCES conversations(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_comm_buyer
        FOREIGN KEY (buyer_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_comm_seller
        FOREIGN KEY (seller_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);