-- =============================================================================
--  VPandit Palmistry — Reference PostgreSQL Schema (core + special-signs ext.)
-- =============================================================================
--  REFERENCE ONLY. The live VPandit app runs on MySQL and self-provisions these
--  tables through the config-driven master engine (config/masters.php) + the
--  PalmistryService seeders. This file is the normalized PostgreSQL equivalent
--  requested by the extension spec, for teams standing up a dedicated CV/ML
--  store or an external microservice DB. It is NOT executed by the Laravel app.
-- =============================================================================

BEGIN;

-- ── Core: lines, characteristic states, line predictions ─────────────────────

CREATE TABLE IF NOT EXISTS palm_lines (
    line_id       SERIAL PRIMARY KEY,
    key_name      VARCHAR(50) UNIQUE NOT NULL,   -- life_line, head_line, heart_line, fate_line, sun_line
    display_name  VARCHAR(100) NOT NULL,
    description   TEXT,
    palm_side     VARCHAR(10) DEFAULT 'BOTH' CHECK (palm_side IN ('BOTH','LEFT','RIGHT')),
    sort_order    INT DEFAULT 0,
    is_active     BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS palm_line_characteristics (
    char_id              SERIAL PRIMARY KEY,
    line_key             VARCHAR(50) NOT NULL REFERENCES palm_lines(key_name) ON DELETE CASCADE,
    state_code           VARCHAR(30) NOT NULL,   -- DEEP_UNBROKEN, STRONG, MODERATE, BROKEN, LIGHT_FAINT, ABSENT
    label                VARCHAR(100) NOT NULL,
    min_presence_score   NUMERIC(3,2) DEFAULT 0,
    min_depth_score      NUMERIC(3,2) DEFAULT 0,
    min_continuity_score NUMERIC(3,2) DEFAULT 0,
    sort_order           INT DEFAULT 0,
    is_active            BOOLEAN DEFAULT TRUE,
    UNIQUE (line_key, state_code)
);

CREATE TABLE IF NOT EXISTS palm_predictions (
    prediction_id        SERIAL PRIMARY KEY,
    line_key             VARCHAR(50) NOT NULL REFERENCES palm_lines(key_name) ON DELETE CASCADE,
    hand_context         VARCHAR(20) DEFAULT 'DOMINANT' CHECK (hand_context IN ('DOMINANT','NON_DOMINANT','LEFT','RIGHT','BOTH')),
    state_code           VARCHAR(30) NOT NULL,
    category             VARCHAR(30) NOT NULL CHECK (category IN ('HEALTH','CAREER','RELATIONSHIPS','FINANCE','SPIRITUALITY','GENERAL')),
    prediction_text      TEXT NOT NULL,
    severity_or_strength INT DEFAULT 3 CHECK (severity_or_strength BETWEEN 1 AND 5),
    sort_order           INT DEFAULT 0,
    is_active            BOOLEAN DEFAULT TRUE
);

-- ── Extension: mounts, special signs, sign predictions ───────────────────────

CREATE TABLE IF NOT EXISTS palm_mounts (
    mount_id      SERIAL PRIMARY KEY,
    key_name      VARCHAR(50) UNIQUE NOT NULL,   -- JUPITER, SATURN, SUN_APOLLO, MERCURY, VENUS, MOON_LUNA, MARS_UPPER, MARS_LOWER, RAHU, KETU
    display_name  VARCHAR(100) NOT NULL,
    ruling_planet VARCHAR(50),
    description   TEXT,
    sort_order    INT DEFAULT 0,
    is_active     BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS palm_special_features (
    feature_id     SERIAL PRIMARY KEY,
    key_name       VARCHAR(50) UNIQUE NOT NULL,  -- STAR, CROSS, TRIANGLE, SQUARE, ISLAND, GRILLE, MOLE_SPOT, FISH, TRISHUL, CHAIN, BAR_INTERRUPTION
    display_name   VARCHAR(100) NOT NULL,
    glyph          VARCHAR(8),
    category       VARCHAR(30) DEFAULT 'SYMBOL' CHECK (category IN ('SYMBOL','SECONDARY_LINE','MOUNT_MARKING')),
    default_impact VARCHAR(20) DEFAULT 'POSITIVE' CHECK (default_impact IN ('POSITIVE','NEGATIVE','AMPLIFIER','MITIGATOR')),
    description    TEXT,
    sort_order     INT DEFAULT 0,
    is_active      BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS palm_special_predictions (
    special_prediction_id SERIAL PRIMARY KEY,
    feature_key           VARCHAR(50) NOT NULL REFERENCES palm_special_features(key_name) ON DELETE CASCADE,
    mount_key             VARCHAR(50) REFERENCES palm_mounts(key_name) ON DELETE SET NULL,
    line_key              VARCHAR(50) REFERENCES palm_lines(key_name) ON DELETE SET NULL,
    hand_context          VARCHAR(20) DEFAULT 'BOTH' CHECK (hand_context IN ('DOMINANT','NON_DOMINANT','LEFT','RIGHT','BOTH')),
    category              VARCHAR(30) NOT NULL CHECK (category IN ('HEALTH','CAREER','RELATIONSHIPS','FINANCE','SPIRITUALITY','GENERAL')),
    prediction_text       TEXT NOT NULL,
    impact_type           VARCHAR(20) DEFAULT 'POSITIVE' CHECK (impact_type IN ('POSITIVE','NEGATIVE','AMPLIFIER','MITIGATOR')),
    severity_or_strength  INT DEFAULT 3 CHECK (severity_or_strength BETWEEN 1 AND 5),
    sort_order            INT DEFAULT 0,
    is_active             BOOLEAN DEFAULT TRUE
);

-- ── Client readings + detected instances ─────────────────────────────────────

CREATE TABLE IF NOT EXISTS client_readings (
    reading_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       INT,
    client_name   VARCHAR(160),
    dominant_hand VARCHAR(5) NOT NULL DEFAULT 'RIGHT' CHECK (dominant_hand IN ('LEFT','RIGHT')),
    left_image    TEXT,
    right_image   TEXT,
    status        VARCHAR(20) DEFAULT 'COMPLETED',
    scores        JSONB,      -- [{hand_side,line_key,presence,depth,continuity,vector}]
    tensions      JSONB,      -- {line_key: tension}
    features      JSONB,      -- [{hand_side,feature_key,mount_key,line_key,confidence,box}]
    result        JSONB,      -- cached analyze() output
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP
);

CREATE TABLE IF NOT EXISTS client_line_scores (
    score_id     SERIAL PRIMARY KEY,
    reading_id   UUID NOT NULL REFERENCES client_readings(reading_id) ON DELETE CASCADE,
    hand_side    VARCHAR(5) NOT NULL CHECK (hand_side IN ('LEFT','RIGHT')),
    line_key     VARCHAR(50) NOT NULL,
    presence     NUMERIC(3,2) NOT NULL,
    depth        NUMERIC(3,2) NOT NULL,
    continuity   NUMERIC(3,2) NOT NULL,
    vector_coordinates JSONB
);

CREATE TABLE IF NOT EXISTS client_detected_features (
    detection_id       SERIAL PRIMARY KEY,
    reading_id         UUID NOT NULL REFERENCES client_readings(reading_id) ON DELETE CASCADE,
    hand_side          VARCHAR(5) NOT NULL CHECK (hand_side IN ('LEFT','RIGHT')),
    feature_key        VARCHAR(50) NOT NULL REFERENCES palm_special_features(key_name),
    mount_key          VARCHAR(50) REFERENCES palm_mounts(key_name),
    line_key           VARCHAR(50) REFERENCES palm_lines(key_name),
    confidence_score   NUMERIC(3,2) NOT NULL,
    bounding_box       JSONB,   -- [x_min,y_min,x_max,y_max] normalized
    vector_coordinates JSONB
);

CREATE INDEX IF NOT EXISTS idx_line_char_line   ON palm_line_characteristics(line_key);
CREATE INDEX IF NOT EXISTS idx_line_pred_line   ON palm_predictions(line_key, state_code);
CREATE INDEX IF NOT EXISTS idx_spec_pred_feat   ON palm_special_predictions(feature_key);
CREATE INDEX IF NOT EXISTS idx_detected_reading ON client_detected_features(reading_id);
CREATE INDEX IF NOT EXISTS idx_scores_reading   ON client_line_scores(reading_id);

COMMIT;

-- Seed data equivalent to PalmistryService::seed*() is applied by the app on
-- MySQL; for this reference store, mirror those INSERTs (mounts, signs, and
-- special predictions) as needed.