"""Image libraries, and per-library access grants.

An image lives in one library; a person is granted a library the way they are
granted a category, so access can be given to some images without giving away
all of them.

Revision ID: d2e3f4a5b6c7
Revises: c1d2e3f4a5b6
"""
from alembic import op

revision = "d2e3f4a5b6c7"
down_revision = "c1d2e3f4a5b6"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS media_libraries (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200) UNIQUE NOT NULL,
        description TEXT,
        user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    """)
    op.execute("""
    CREATE TABLE IF NOT EXISTS media_library_grants (
        id SERIAL PRIMARY KEY,
        library_id INTEGER NOT NULL REFERENCES media_libraries(id) ON DELETE CASCADE,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        granted_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT uq_media_library_grant UNIQUE (library_id, user_id)
    )
    """)
    op.execute("ALTER TABLE media_assets ADD COLUMN IF NOT EXISTS library_id INTEGER REFERENCES media_libraries(id) ON DELETE SET NULL")
    op.execute("ALTER TABLE media_assets ADD COLUMN IF NOT EXISTS storage VARCHAR(10) NOT NULL DEFAULT 'local'")
    op.execute("ALTER TABLE media_assets ADD COLUMN IF NOT EXISTS byte_size INTEGER")
    op.execute("CREATE INDEX IF NOT EXISTS ix_media_library ON media_assets(library_id)")
    op.execute("""
    INSERT INTO media_libraries (name, description)
    VALUES ('General', 'Images that have not been sorted into a library yet')
    ON CONFLICT (name) DO NOTHING
    """)


def downgrade():
    op.execute("DROP INDEX IF EXISTS ix_media_library")
    op.execute("ALTER TABLE media_assets DROP COLUMN IF EXISTS byte_size")
    op.execute("ALTER TABLE media_assets DROP COLUMN IF EXISTS storage")
    op.execute("ALTER TABLE media_assets DROP COLUMN IF EXISTS library_id")
    op.execute("DROP TABLE IF EXISTS media_library_grants")
    op.execute("DROP TABLE IF EXISTS media_libraries")