-- CannaManage V1 Initial Schema
-- Implements all tables required for CanG §19 compliance tracking

-- Clubs (root of tenant hierarchy)
CREATE TABLE clubs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL,
    name            VARCHAR(255) NOT NULL,
    address         TEXT,
    license_number  VARCHAR(100) NOT NULL UNIQUE,
    max_members     INT NOT NULL DEFAULT 500,
    status          VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Members
CREATE TABLE members (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    club_id             UUID NOT NULL REFERENCES clubs(id),
    first_name          VARCHAR(100) NOT NULL,
    last_name           VARCHAR(100) NOT NULL,
    email               VARCHAR(255) NOT NULL,
    date_of_birth       DATE NOT NULL,
    membership_date     DATE NOT NULL DEFAULT CURRENT_DATE,
    membership_number   VARCHAR(50) NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
    is_under_21         BOOLEAN NOT NULL DEFAULT FALSE,
    prevention_officer  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(email, tenant_id),
    UNIQUE(membership_number, tenant_id)
);

-- Strains
CREATE TABLE strains (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL,
    name            VARCHAR(255) NOT NULL,
    thc_percentage  NUMERIC(5,2) NOT NULL,
    cbd_percentage  NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    description     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Batches
CREATE TABLE batches (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    strain_id           UUID NOT NULL REFERENCES strains(id),
    quantity_grams      NUMERIC(10,2) NOT NULL,
    harvest_date        DATE,
    batch_code          VARCHAR(100) NOT NULL,
    status              VARCHAR(50) NOT NULL DEFAULT 'AVAILABLE',
    contamination_flag  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(batch_code, tenant_id)
);

-- Distributions (immutable — append-only for CanG §26 compliance)
CREATE TABLE distributions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL,
    member_id       UUID NOT NULL REFERENCES members(id),
    batch_id        UUID NOT NULL REFERENCES batches(id),
    quantity_grams  NUMERIC(10,2) NOT NULL,
    distributed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    recorded_by     UUID NOT NULL REFERENCES members(id),
    notes           TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Monthly quotas (one row per member per calendar month)
CREATE TABLE monthly_quotas (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    member_id           UUID NOT NULL REFERENCES members(id),
    year                INT NOT NULL,
    month               INT NOT NULL CHECK (month >= 1 AND month <= 12),
    total_distributed   NUMERIC(10,2) NOT NULL DEFAULT 0.00,
    max_allowed         NUMERIC(10,2) NOT NULL,
    version             BIGINT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(member_id, year, month)
);

-- Stock movements (audit journal)
CREATE TABLE stock_movements (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL,
    batch_id        UUID NOT NULL REFERENCES batches(id),
    movement_type   VARCHAR(50) NOT NULL,
    quantity_grams  NUMERIC(10,2) NOT NULL,
    reason          TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Users (login identities — Sprint 2 auth)
CREATE TABLE users (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id           UUID NOT NULL,
    member_id           UUID REFERENCES members(id),
    email               VARCHAR(255) NOT NULL,
    password_hash       VARCHAR(255) NOT NULL,
    role                VARCHAR(50) NOT NULL DEFAULT 'ROLE_MEMBER',
    last_login          TIMESTAMPTZ,
    active              BOOLEAN NOT NULL DEFAULT TRUE,
    refresh_token_hash  VARCHAR(255),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(email, tenant_id)
);

-- Performance indexes
CREATE INDEX idx_members_club_id ON members(club_id);
CREATE INDEX idx_members_tenant_id ON members(tenant_id);
CREATE INDEX idx_distributions_member_id ON distributions(member_id);
CREATE INDEX idx_distributions_tenant_id ON distributions(tenant_id);
CREATE INDEX idx_distributions_distributed_at ON distributions(distributed_at);
CREATE INDEX idx_monthly_quotas_member_month ON monthly_quotas(member_id, year, month);
CREATE INDEX idx_batches_tenant_status ON batches(tenant_id, status);
